Edit output from a measuring machine

HotLanta

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

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top