Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to update last character
We are using Excel 2007. We have a large list of id numbers that will need
the last character changed from a 1 to 2. The length of the list will always vary but 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
Sorry about the double post.I got an error the first time so didn't think it
got posted. -- maryj "maryj" wrote: We are using Excel 2007. We have a large list of id numbers that will need the last character changed from a 1 to 2. The length of the list will always vary but 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
Hi,
Try this. Change Sht variable to the name of your sheet Sub last_Char() Dim LastRow As Long Dim MyRange As Range, c As Range Set sht = Sheets("Sheet2") ' change to suit LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = sht.Range("G1:G" & LastRow) For Each c In MyRange c.Value = Left(c.Value, Len(c.Value) - 1) & _ Right(c.Value, 1) + 1 Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "maryj" wrote: We are using Excel 2007. We have a large list of id numbers that will need the last character changed from a 1 to 2. The length of the list will always vary but will always be in Column G. For example: 11323674US01 11321507US01 11378181US01 need to be updated to: 11323674US02 11321507US02 11378181US02 Thanks for your help! -- maryj |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to update last character
Hi,
On reflection I should have included a chack to ensure the rightmost character was numeric Sub last_Char() Dim LastRow As Long Dim MyRange As Range, c As Range Set sht = Sheets("Sheet2") ' change to suit LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = sht.Range("G1:G" & LastRow) For Each c In MyRange If IsNumeric(Right(c.Value, 1)) Then c.Value = Left(c.Value, Len(c.Value) - 1) & _ Right(c.Value, 1) + 1 End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Try this. Change Sht variable to the name of your sheet Sub last_Char() Dim LastRow As Long Dim MyRange As Range, c As Range Set sht = Sheets("Sheet2") ' change to suit LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = sht.Range("G1:G" & LastRow) For Each c In MyRange c.Value = Left(c.Value, Len(c.Value) - 1) & _ Right(c.Value, 1) + 1 Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "maryj" wrote: We are using Excel 2007. We have a large list of id numbers that will need the last character changed from a 1 to 2. The length of the list will always vary but will always be in Column G. For example: 11323674US01 11321507US01 11378181US01 need to be updated to: 11323674US02 11321507US02 11378181US02 Thanks for your help! -- maryj |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to update last character
Assuming the values are always stored in column G why don't you add (in any
other empty column) formula as follows: =IF(NOT(ISBLANK(G1)),CONCATENATE(LEFT(G1,LEN(G1)-1),"2"),"") The formula shows empty string if there is now value in column G and it also assumes that the length of string in column G may vary. Hope it helps. Kind regards IgorM "maryj" wrote in message ... We are using Excel 2007. We have a large list of id numbers that will need the last character changed from a 1 to 2. The length of the list will always vary but will always be in Column G. For example: 11323674US01 11321507US01 11378181US01 need to be updated to: 11323674US02 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to update last character
Thanks Mike! Work like a dream!!!
-- maryj "Mike H" wrote: Hi, Try this. Change Sht variable to the name of your sheet Sub last_Char() Dim LastRow As Long Dim MyRange As Range, c As Range Set sht = Sheets("Sheet2") ' change to suit LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = sht.Range("G1:G" & LastRow) For Each c In MyRange c.Value = Left(c.Value, Len(c.Value) - 1) & _ Right(c.Value, 1) + 1 Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "maryj" wrote: We are using Excel 2007. We have a large list of id numbers that will need the last character changed from a 1 to 2. The length of the list will always vary but will always be in Column G. For example: 11323674US01 11321507US01 11378181US01 need to be updated to: 11323674US02 11321507US02 11378181US02 Thanks for your help! -- maryj |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to update last character
Glad I could help and thanks for the feedback
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "maryj" wrote: Thanks Mike! Work like a dream!!! -- maryj "Mike H" wrote: Hi, Try this. Change Sht variable to the name of your sheet Sub last_Char() Dim LastRow As Long Dim MyRange As Range, c As Range Set sht = Sheets("Sheet2") ' change to suit LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = sht.Range("G1:G" & LastRow) For Each c In MyRange c.Value = Left(c.Value, Len(c.Value) - 1) & _ Right(c.Value, 1) + 1 Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "maryj" wrote: We are using Excel 2007. We have a large list of id numbers that will need the last character changed from a 1 to 2. The length of the list will always vary but will always be in Column G. For example: 11323674US01 11321507US01 11378181US01 need to be updated to: 11323674US02 11321507US02 11378181US02 Thanks for your help! -- maryj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update last character | Excel Programming | |||
Help - macro messes up the character formating | Excel Programming | |||
Macro to delete 'tick' Character | Excel Programming | |||
Hidden Character Find Macro Help | Excel Programming | |||
Setting character colour in a macro | Excel Discussion (Misc queries) |