Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to update last character
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! -- maryj |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to update last character
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 -- Cheers, Ryan "maryj" wrote: 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! -- maryj |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to update last character
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help - macro messes up the character formating | Excel Programming | |||
Macro that deletes the last character in a cell | Excel Programming | |||
Hidden Character Find Macro Help | Excel Programming | |||
How can I use a Macro to set a Page break after a character. | Excel Programming | |||
Setting character colour in a macro | Excel Discussion (Misc queries) |