Incrementing a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Francis Hookham - 23 Sep 2007 09:02 GMT

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

Reply to this Message

Joel - 23 Sep 2007 10:32 GMT

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

> Column C contains string IDs with a varying number of blank cells between
> each.
[quoted text clipped - 11 lines]
>
> Francis Hookham

Reply to this Message

Francis Hookham - 23 Sep 2007 17:27 GMT

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

> You have to split the string into two parts. Then increment the number
> and
[quoted text clipped - 34 lines]
>>
>> Francis Hookham

Reply to this Message

Bob Phillips - 23 Sep 2007 10:39 GMT

sZeroes = "0000000000"
sSuffix = Right(sDoorname, Len(sDoorname) - InStr(sDoorname, "-"))
sNextDoorname = Left(sDoorname, InStr(sDoorname, "-")) & Format(sSuffix +
1, Left(sZeroes, Len(sSuffix)))

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Column C contains string IDs with a varying number of blank cells between
> each.
[quoted text clipped - 11 lines]
>
> Francis Hookham

Reply to this Message

Francis Hookham - 23 Sep 2007 17:28 GMT

Thank you Bob - great - very smooooth!

Francis

> sZeroes = "0000000000"
> sSuffix = Right(sDoorname, Len(sDoorname) - InStr(sDoorname, "-"))
[quoted text clipped - 16 lines]
>>
>> Francis Hookham

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Sep 2007 20:44 GMT

> Column C contains string IDs with a varying number of blank cells between
> each.
[quoted text clipped - 9 lines]
> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Sep 2007 20:53 GMT

See inline comment...

>> Column C contains string IDs with a varying number of blank cells between
>> each.
[quoted text clipped - 12 lines]
> 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
[quoted text clipped - 6 lines]
>
> Rick

Reply to this Message

Francis Hookham - 27 Sep 2007 17:31 GMT

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

> See inline comment...
>
[quoted text clipped - 33 lines]
>>
>> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 27 Sep 2007 19:12 GMT

>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 to this Message

Francis Hookham - 30 Sep 2007 16:12 GMT

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

Francis

> >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
[quoted text clipped - 4 lines]
>
> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 24 Sep 2007 06:16 GMT

>> Column C contains string IDs with a varying number of blank cells between
>> each.
[quoted text clipped - 21 lines]
> 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

Reply to this Message