Macro to update last character

  • Thread starter Thread starter maryj
  • Start date Start date
M

maryj

We are using Excel 2007. We will have a list of id numbers that we need the
last number to update from a 1 to a 2. The length of the list will vary each
time but the values will always be in column G.
For example:

11323674US01
11321507US01
11378181US01

Need to be updated to:

11323674US02
11321507US02
11378181US02

Thanks for your help!
 
Hope this helps! If so, let me know, click "YES" below.

Sub ReplaceLastCharacter()

Dim LastRow As Long
Dim MyRange As Range
Dim cell As Range

LastRow = Cells(Rows.Count, "G").End(xlUp).Row
Set MyRange = Range("G1:G" & LastRow)
For Each cell In MyRange
cell.Value = Left(cell.Value, Len(cell.Value) - 1) & "2"
Next cell

End Sub
 
And just in case you need yet another way to do it, it can be done by
creating an array based on the range (in theory an array is more efficient,
but to see a difference it depends on the size of the dataset).

As an example:

Sub Update12()

Dim vID As Variant
With Sheet1
vID = .Range(.Range("G1"), .Range("G1").End(xlDown)).Value
End With

Dim b As Integer
b = UBound(vID, 1) '=3

Dim e As Integer
Dim f As String
Dim g As String

For e = 1 To b
f = CStr(vID(e, 1))
g = Right(f, 1)

If Right(f, 1) = "1" Then
g = "2"
End If

vID(e, 1) = Left(f, Len(f) - 1) & g
Sheet1.Cells(e, "G").Value = vID(e, 1)

Next e

End Sub

Just something to consider. Either should work fine.
 
Back
Top