Edit output from a measuring machine

[HotLanta]

Hi!

I have data that is output from a measuring machine.

The machine outputs numerical data with a one letter descriptor of direction into each cell.
For example in row one I have 0.1 H, 0.2 B, 0.1 I, 0.4 F
All of the results are positive numbers with a descriptor.

The descriptor tells me that the result was 0.1 High, 0.2 Back, 0.1 Inboard, 0.4 Forward, and so on.

To do analysis on the data I need to get rid of the descriptor... easy enough. I can run a macro to replace F,B.I,O,U,D.

What I want to do however is in the case of I, F, and D - I want the numbers in these cells to become negative numbers.
So in the case above I would see in row one: 0.1, 0.2, -0.1, -0.4.

Any ideas? Thanks!

 

[Jeffrey Mahoney]

You might want to replace the line:
For Each Cel In Selection
with an actual range like:
For Each Cel In Range("A2:H1000")

Sub ReplaceLetters()
  Dim Cel As Range
  Dim A As String
  Dim Ltr As String
  
  For Each Cel In Selection
    A = Cel.Value
    Ltr = Right(A, 1)
    Select Case Ltr
      Case "I", "F", "D"
        Cel.Value = Val(A) * -1
      Case Else
        Cel.Value = Val(A)
    End Select
  Next Cel
End Sub

 

[HotLanta]

Jeffrey,

Thank you for your reply. I tested this code and it is working perfectly save for one thing... When running on my selection of cells it returns a zero value in any cell that was blank before running the macro. Is there any way to ignore blank cells or compensate in some way? thank you for your help!

 

[Jeffrey Mahoney]

Here you go.

Sub ReplaceLetters()
  Dim Cel As Range
  Dim A As String
  Dim Ltr As String
  
  For Each Cel In Selection
    A = Cel.Value
    If A <> "" Then
      Ltr = Right(A, 1)
      Select Case Ltr
        Case "I", "F", "D"
          Cel.Value = Val(A) * -1
        Case Else
          Cel.Value = Val(A)
      End Select
    End If
  Next Cel
End Sub