replacing dynamic currency amounts with a character

[Tony]

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

 

[stevebriz]

what criteria do you use for determine what the last character will be
replaced by??

eg. if the last charcacter is a 1 do your replace it with a "A"
if the last charcacter is a 2 do your replace it with a "B"

 

[Guest]

Assuming the character is not a number and that it is actually stored in the
cell (making the whole cell a text string) rather than produced through
formatting.

select the column and do Edit=>Replace

What: }
With: J

 

[Tony]

what criteria do you use for determine what the last character will be
replaced by??

eg. if the last charcacter is a 1 do your replace it with a "A"
if the last charcacter is a 2 do your replace it with a "B"

thanks for the reply. the citeria will always be:

1=J
2=K
3=L
4=M
5=N
6=O
7=P
8=Q
9=R
0=}

i really appreciate it!

 

[Tony]

this would be to time consuming to do daily.

Assuming the character is not a number and that it is actually stored in the
cell (making the whole cell a text string) rather than produced through
formatting.

select the column and do Edit=>Replace

What: }
With: J

 

[Tony]

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.

 

[stevebriz]

hi tony
I just did something similar...
has another check..
1/ accounts for if the number that end like 0.20 has only 0.2
showing...

Dim i As Integer ' for rows
Dim j As Integer ' for columns
Sheet1.Activate
Sheet1.Cells(1, 1).Select
' lets assume the number you want to change is is rows 1-20 in column
1(A)on sheet 1
Sheet1.Activate ' show sheet 1
Sheet1.Cells(1, 1).Select ' select sheet 1 A1
j = 1 ' 1 = column A
For i = 1 To 20 ' rows 1 to 20
If Cells(i, j).Value = vbNullString Then ' check the cell is
not empty
Else
If Not IsNumeric(Right$(Cells(i, j).Value, 1)) Then '
checks last char is a number

Else
' this to cover if the zeros are dropped after
the decimal point

If Mid(Cells(i, j).Value, ((Len(Cells(i,
j).Value) - 2)), 1) <> "." Then
Cells(i, j).Value = Cells(i, j).Value & "}"
Else
' truncates the string and adds the correct
character from function
Cells(i, j).Value = Left$(Cells(i, j).Value,
Len(Cells(i, j).Value) - 1) & newlastchar(Right$(Cells(i, j).Value, 1))
End If
End If

End If
Next i
End Sub

Function newlastchar(ln As Integer)

Select Case ln
Case 1
newlastchar = "J"
Case 2
newlastchar = "K"
Case 3
newlastchar = "L"
Case 4
newlastchar = "M"
Case 5
newlastchar = "N"
Case 6
newlastchar = "O"
Case 7
newlastchar = "P"
Case 8
newlastchar = "Q"
Case 9
newlastchar = "R"
Case 0
newlastchar = "}"

End Select
End Function