Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
Column C contains string IDs with a varying number of blank cells between
each. Having found the last entry in column E and the latest ID with: iDoorRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row sDoorName = Sheets("Pages").Cells(iDoorRow, 5) how can I increment the string (sDoorName) (in this case D02-003) to D02-004? Then I can prompt the user to confirm that as the next door or to type in D03-001 to start the next sequence or whatever. Francis Hookham |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
You have to split the string into two parts. Then increment the number and
put the two parts back together. Look at the code below. Sub test() 'used A1 for my testing sDoorName = Range("A1").Value 'get everything left of the dash and the dash sDoorPrefix = Left(sDoorName, _ InStr(sDoorName, "-")) 'get everything left of the dash ' and convert to number sDoorNum = Val(Mid(sDoorName, _ InStr(sDoorName, "-") + 1)) 'add 1 to number sDoorNum = sDoorNum + 1 'format number so it contain leading zeros sDoorNumStr = Format(sDoorNum, "0##") 'put the string back together NewsDoorName = sDoorPrefix & sDoorNumStr End Sub "Francis Hookham" wrote: Column C contains string IDs with a varying number of blank cells between each. Having found the last entry in column E and the latest ID with: iDoorRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row sDoorName = Sheets("Pages").Cells(iDoorRow, 5) how can I increment the string (sDoorName) (in this case D02-003) to D02-004? Then I can prompt the user to confirm that as the next door or to type in D03-001 to start the next sequence or whatever. Francis Hookham |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
sZeroes = "0000000000"
sSuffix = Right(sDoorname, Len(sDoorname) - InStr(sDoorname, "-")) sNextDoorname = Left(sDoorname, InStr(sDoorname, "-")) & Format(sSuffix + 1, Left(sZeroes, Len(sSuffix))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Francis Hookham" wrote in message ... Column C contains string IDs with a varying number of blank cells between each. Having found the last entry in column E and the latest ID with: iDoorRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row sDoorName = Sheets("Pages").Cells(iDoorRow, 5) how can I increment the string (sDoorName) (in this case D02-003) to D02-004? Then I can prompt the user to confirm that as the next door or to type in D03-001 to start the next sequence or whatever. Francis Hookham |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
Many thanks Joel - yours and Bob's look much the same - he has strung things
together - I'll try both. I am most grateful Francis "Joel" wrote in message ... You have to split the string into two parts. Then increment the number and put the two parts back together. Look at the code below. Sub test() 'used A1 for my testing sDoorName = Range("A1").Value 'get everything left of the dash and the dash sDoorPrefix = Left(sDoorName, _ InStr(sDoorName, "-")) 'get everything left of the dash ' and convert to number sDoorNum = Val(Mid(sDoorName, _ InStr(sDoorName, "-") + 1)) 'add 1 to number sDoorNum = sDoorNum + 1 'format number so it contain leading zeros sDoorNumStr = Format(sDoorNum, "0##") 'put the string back together NewsDoorName = sDoorPrefix & sDoorNumStr End Sub "Francis Hookham" wrote: Column C contains string IDs with a varying number of blank cells between each. Having found the last entry in column E and the latest ID with: iDoorRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row sDoorName = Sheets("Pages").Cells(iDoorRow, 5) how can I increment the string (sDoorName) (in this case D02-003) to D02-004? Then I can prompt the user to confirm that as the next door or to type in D03-001 to start the next sequence or whatever. Francis Hookham |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
Thank you Bob - great - very smooooth!
Francis "Bob Phillips" wrote in message ... sZeroes = "0000000000" sSuffix = Right(sDoorname, Len(sDoorname) - InStr(sDoorname, "-")) sNextDoorname = Left(sDoorname, InStr(sDoorname, "-")) & Format(sSuffix + 1, Left(sZeroes, Len(sSuffix))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Francis Hookham" wrote in message ... Column C contains string IDs with a varying number of blank cells between each. Having found the last entry in column E and the latest ID with: iDoorRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row sDoorName = Sheets("Pages").Cells(iDoorRow, 5) how can I increment the string (sDoorName) (in this case D02-003) to D02-004? Then I can prompt the user to confirm that as the next door or to type in D03-001 to start the next sequence or whatever. Francis Hookham |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
Column C contains string IDs with a varying number of blank cells between
each. Having found the last entry in column E and the latest ID with: iDoorRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row sDoorName = Sheets("Pages").Cells(iDoorRow, 5) how can I increment the string (sDoorName) (in this case D02-003) to D02-004? Then I can prompt the user to confirm that as the next door or to type in D03-001 to start the next sequence or whatever. You can use this function to return the next number in the sequence (if it is 000 Function NextName(sDoorname As String) As String NextName = sDoorname Mid(NextName, 5) = Format((Val(Mid(NextName, 5)) + 1) Mod 1000, "000") End Function Or, if you are not repeating the process several times, embed the two lines from the function directly in your code (making sure you Dim the NextName variable in that case). Rick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
See inline comment...
"Rick Rothstein (MVP - VB)" wrote in message ... Column C contains string IDs with a varying number of blank cells between each. Having found the last entry in column E and the latest ID with: iDoorRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row sDoorName = Sheets("Pages").Cells(iDoorRow, 5) how can I increment the string (sDoorName) (in this case D02-003) to D02-004? Then I can prompt the user to confirm that as the next door or to type in D03-001 to start the next sequence or whatever. You can use this function to return the next number in the sequence (if it is 000 The above sentence was supposed to say, at the end, "if it returns "000", it means the inputted DoorName ended with 999, so there isn't a next number unless you have code to automatically switch to the next sequence". Rick Function NextName(sDoorname As String) As String NextName = sDoorname Mid(NextName, 5) = Format((Val(Mid(NextName, 5)) + 1) Mod 1000, "000") End Function Or, if you are not repeating the process several times, embed the two lines from the function directly in your code (making sure you Dim the NextName variable in that case). Rick |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
Column C contains string IDs with a varying number of blank cells between
each. Having found the last entry in column E and the latest ID with: iDoorRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row sDoorName = Sheets("Pages").Cells(iDoorRow, 5) how can I increment the string (sDoorName) (in this case D02-003) to D02-004? Then I can prompt the user to confirm that as the next door or to type in D03-001 to start the next sequence or whatever. You can use this function to return the next number in the sequence (if it is 000 Function NextName(sDoorname As String) As String NextName = sDoorname Mid(NextName, 5) = Format((Val(Mid(NextName, 5)) + 1) Mod 1000, "000") End Function Or, if you are not repeating the process several times, embed the two lines from the function directly in your code (making sure you Dim the NextName variable in that case). One other comment on the function I posted... I assumed you would be checking to see if the maximum number of "doornames" had been assigned **after** you tried to increment it. However, that is not necessary and it occurred to me that you are probably already checking for that condition before trying to increment the "doorname". If that is the case, then the Mod operation can be removed from my code, making it noticeably shorter.... Function NextName(sDoorname As String) As String NextName = sDoorname Mid(NextName, 5) = Format(Val(Mid(NextName, 5)) + 1, "000") End Function And, although I prefer coercing data types manually (hence, the Val function call), you could let VB do it for you relatively safely in this particular case... Function NextName(sDoorname As String) As String NextName = sDoorname Mid(NextName, 5) = Format(Mid(NextName, 5) + 1, "000") End Function thus shortening the code even more. Rick |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
A belated thank you Rick - I have never got the hang of defining functions -
Bob Philip's answer does what I want but I shall try to understand the function later. Bets wishes Francis "Rick Rothstein (MVP - VB)" wrote in message ... See inline comment... "Rick Rothstein (MVP - VB)" wrote in message ... Column C contains string IDs with a varying number of blank cells between each. Having found the last entry in column E and the latest ID with: iDoorRow = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row sDoorName = Sheets("Pages").Cells(iDoorRow, 5) how can I increment the string (sDoorName) (in this case D02-003) to D02-004? Then I can prompt the user to confirm that as the next door or to type in D03-001 to start the next sequence or whatever. You can use this function to return the next number in the sequence (if it is 000 The above sentence was supposed to say, at the end, "if it returns "000", it means the inputted DoorName ended with 999, so there isn't a next number unless you have code to automatically switch to the next sequence". Rick Function NextName(sDoorname As String) As String NextName = sDoorname Mid(NextName, 5) = Format((Val(Mid(NextName, 5)) + 1) Mod 1000, "000") End Function Or, if you are not repeating the process several times, embed the two lines from the function directly in your code (making sure you Dim the NextName variable in that case). Rick |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
A belated thank you Rick - I have never got the hang of defining
functions - Bob Philip's answer does what I want but I shall try to understand the function later. If you have any questions on how the function I posted work, please feel free to ask. Rick |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing a string
Many thanks - what would we do without you amazing MVPs? - we simply should
not be able to achieve what we do. Francis "Rick Rothstein (MVP - VB)" wrote in message ... A belated thank you Rick - I have never got the hang of defining functions - Bob Philip's answer does what I want but I shall try to understand the function later. If you have any questions on how the function I posted work, please feel free to ask. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
incrementing | Excel Worksheet Functions | |||
incrementing | Excel Discussion (Misc queries) | |||
incrementing every two rows | Excel Programming | |||
Next not incrementing | Excel Programming | |||
incrementing on inserrt row | Excel Programming |