Incrementing a string

[Francis Hookham]

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

 

[Guest]

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

 

[Bob Phillips]

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]

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

 

[Francis Hookham]

Thank you Bob - great - very smooooth!

Francis

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)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

See inline comment...

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

 

[Rick Rothstein \(MVP - VB\)]

Column C contains string IDs with a varying number of blank cells between

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

 

[Francis Hookham]

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\)]

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

 

[Francis Hookham]

Many thanks - what would we do without you amazing MVPs? - we simply should
not be able to achieve what we do.

Francis