Edit output from a measuring machine

HotLanta

Board Regular
Joined
Nov 3, 2005
Messages
156
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!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
You might want to replace the line:
For Each Cel In Selection
with an actual range like:
For Each Cel In Range("A2:H1000")

Code:
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

Board Regular
Joined
Nov 3, 2005
Messages
156
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

Well-known Member
Joined
May 31, 2015
Messages
1,768
Here you go.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,913
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top