replacing dynamic currency amounts with a character
Tony wrote:
Hello all,
I have a request from a Business Unit and I am baffled.
We will be getting an excel spreadsheet each day and the amounts in a
particular column will change as will the number of rows.
i need to replace the last character in the amount with a particular
alpha character.
example:
20.50 = 20.5}
65.01 = 65.0J
it only changes the last penny.
if anyone could help on this, i would greatly appreciate it. i am not
savvy in this.
thanks,
tony
All,
I figured it out:
Sub UpdateValues()
Dim rng As Range, cell As Range, s As String
Dim i As Long
Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
Dim oldVal, newVal, o As String
For Each cell In rng
s = Right(cell.Value, 1)
o = Len(cell.Value)
oldVal = Left(cell.Value, o - 1)
Select Case s
Case 1
newVal = Replace(s, "1", "J")
cell.Value = oldVal + newVal
Case 2
newVal = Replace(s, "2", "K")
cell.Value = oldVal + newVal
Case 3
newVal = Replace(s, "3", "L")
cell.Value = oldVal + newVal
Case 4
newVal = Replace(s, "4", "M")
cell.Value = oldVal + newVal
Case 5
newVal = Replace(s, "5", "N")
cell.Value = oldVal + newVal
Case 6
newVal = Replace(s, "6", "O")
cell.Value = oldVal + newVal
Case 7
newVal = Replace(s, "7", "P")
cell.Value = oldVal + newVal
Case 8
newVal = Replace(s, "8", "Q")
cell.Value = oldVal + newVal
Case 9
newVal = Replace(s, "9", "R")
cell.Value = oldVal + newVal
Case 0
newVal = Replace(s, "0", "}")
cell.Value = oldVal + newVal
End Select
Next
End Sub
thanks all for help.
|