Thank very much - I am sure I shall be able to do that.

Francis


"Joel" wrote in message
...
The code doesn't need to be changed as long as you set sDNum = suffix part
of
the string. This is basically the code I posted during your previous
requests.


Get the prefix from ABC (ABC is a cell or another variable)
pDNum = left(ABC,instr(ABC,"-") - 1)
sDNum = mid(ABC,instr(ABC,"-") + 1)

then run code below which put incremented string into sDNumNext
now put string back together

newstring = pDNum & sDNumNext

I can't tell from this piece of code how to make the complete fix so I
gave
you the pieces to do yourself.



"Francis Hookham" wrote:

Wow! That's great. Works a treat.

(If you really wanted to put the icing on the cake, I need to work out
the
next floor level.
Floor levels are D## followed by a '-' then the door number ###
So ground floor door number 3 is D01-003
In the InputBox I could put a note saying something like 'Backspace to be
left blank to start next door'
If sDNumNext is "" then work out the next floor level and make sDNumNext
=
D02-001
I've just got to work out how to increment the ## by 1)

I really am most grateful for what you have done so quickly - in the time
it
takes to catch the bus downtown for a flu jab and come home!

Francis


"Joel" wrote in message
...
Try this code and see if it works better than chips code. chip code
doesn't
handle all the possible cases.

Sub PrepareForDoor()
'GET LAST DOOR ROW AND NUMBER
iDNRow = Sheets("Store").Cells(1, 1)
sDNum = Cells(iDNRow, 5)
sDNumNext = sDNum
If IsNumeric(sDNumNext) Then
sDNumNext = Val(sDNumNext)
'add 1 to number
sDNumNext = sDNumNext + 1
'format number so it contain leading zeros
sDNumNext = Format(sDNumNext, "0##")
Else
carry = 1
stringlen = Len(sDNumNext)
For i = stringlen To (stringlen - 2) Step -1
char = Mid(sDNumNext, i, 1)
Select Case char
Case "0" To "8"
newchar = Chr(Asc(char) + 1)
carry = 0
Case "9"
newchar = "a"
carry = 0
Case "a" To "y", "A" To "Z"
newchar = Chr(Asc(char) + 1)
carry = 0
Case "z", "Z"
newchar = "0"
carry = 1
End Select
Mid(sDNumNext, i, 1) = newchar
If carry = 0 Then Exit For
Next i
End If

sDNumNext = InputBox("This is next door number" _
& vbNewLine & "following the last door entered." _
& vbNewLine & "Check carefully before clicking 'OK'" _
& vbNewLine & "or change it first if you are" _
& vbNewLine & " inserting a door" _
& vbNewLine & " or" _
& vbNewLine & " starting another floor." _
& vbNewLine, _
"Next door number?", sDNumNext, 2)

'etc
'etc
End Sub


"Francis Hookham" wrote:

Chip,
I must have got something wrong in
sDNum = Left(sDNum, Len(sDNum) - 1) & CHAR(CODE(Right(sDNum,
1))
+ 1
+ (6 * (Right(sDNum, 1) = "Z")))

What I do not understand is lowercase 'CHAR(CODE' does not become
upper
case
as I would expect. So, is there a mistake there.

The code I am trying to get right is below. I am trying to increment
door
numbers. Some have a suffix and some not so
D01-012 will change to D01-013
D02-004a will change to D02-004b

Please......

Francis

Sub PrepareForDoor()
'GET LAST DOOR ROW AND NUMBER
iDNRow = Sheets("Store").Cells(1, 1)
sDNum = Cells(iDNRow, 5)
'IS IT AN ORDINARY OR INSERTED DOOR?
If Len(sDNum) = 7 Then
'INCREMENT DOOR NUMBER
sZeroes = "0000000000"
sSuffix = Right(sDNum, Len(sDNum) - InStr(sDNum, "-"))
sDNum = Left(sDNum, InStr(sDNum, "-")) & _
Format(sSuffix + 1, Left(sZeroes, Len(sSuffix)))
sDNumNext = sDNum
Else
'INCREMENT DOOR NUMBER SUFFIX
sDNum = Left(sDNum, Len(sDNum) - 1) & CHAR(CODE(Right(sDNum,
1))
+ 1
+ (6 * (Right(sDNum, 1) = "Z")))
sDNumNext = sDNum
End If
'ASK IF THIS IS NEXT DOOR NUMBER
sDNumNext = InputBox("This is next door number" _
& vbNewLine & "following the last door entered." _
& vbNewLine & "Check carefully before clicking 'OK'" _
& vbNewLine & "or change it first if you are" _
& vbNewLine & " inserting a door" _
& vbNewLine & " or" _
& vbNewLine & " starting another floor." _
& vbNewLine, _
"Next door number?", sDNumNext, 2)

'etc
'etc
End Sub


"Chip Pearson" wrote in message
...
Francis,

The following formula and VBA function will incrment A - Z - a -
z.
What should happen if the last character is a lower case 'z'?


=LEFT(A1,LEN(A1)-1)&CHAR(CODE(RIGHT(A1,1))+1+(6*(RIGHT(A1,1)="Z")) )


Function IncrAlpha(InChars As String) As String
IncrAlpha = Left(InChars, Len(InChars) - 1) & _
Chr(Asc(Right(InChars, 1)) + 1 - (6 * (Right(InChars, 1) =
"Z")))
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Francis Hookham" wrote in message
...
Earlier postings have shown me how to increment a string where the
last
characters are numerals so

D01-004

can be incremented to

D01-005

(see code below)



I also need to be able to increment the last character when it is a
letter rather than a number, for instance,

D01-006c

incremented to

D01-006d



Any suggestions?



Francis Hookham



Code used to increment D01-004 to D01-005 is

sZeroes = "0000000000"

sSuffix = Right(sDNum, Len(sDNum) - InStr(sDNum, "-"))

sDNum = Left(sDNum, InStr(sDNum, "-")) & _

Format(sSuffix + 1, Left(sZeroes, Len(sSuffix)))