Find / Replace / * -1
If each entry only has one letter, then this may work for you:
Public Sub ReplaceAlphas()
Dim rCell As Range
Dim sRightChar As String
Application.EnableEvents = False
On Error Resume Next
For Each rCell In Columns(1).Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With rCell
sRightChar = Right(.Text, 1)
If sRightChar Like "[A-Z]" Then _
.Value = -(Left(.Text, Len(.Text) - 1) & _
Asc(sRightChar) - 64)
End With
Next rCell
On Error GoTo 0
End Sub
In article ,
wrote:
I have a column with numbers. The numbers end in alpha
(A, B, C...). I need to search the column and find 'A'
replace with the number 1 and then multiply by -1. Do
this for all the A's, then search for 'B' and replace
with the number 2 and multiply by -1 and so forth.
Any thoughts on how to do this?
|