| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing an alpha character
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))) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
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))) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
Many thanks Chip - no problem - the number of increments is unlikely to
exceed half a dozen. D10-001 etc are door numbers in a schedule - the alpha suffix is only used when a door is lotted in later. I just wanted to be able to slot in a few more is necessary. If more than a few, total renumbering would be justified. I am most grateful Francis "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))) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
Francis: Chips code has a few problems. What if the last character is a
number? If you have any problems try this code. It also handles the case when you get to z it will cause a carry and increment the next character. It also handles numbers and letters as last character If IsNumeric(sDNum) Then sDoorNum = Val(sDNum) 'add 1 to number sDoorNum = sDoorNum + 1 'format number so it contain leading zeros sDNum = Format(sDoorNum, "0##") Else carry = 1 stringlen = Len(sDNum) For i = stringlen To (stringlen - 2) Step -1 char = Mid(sDNum, 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(sDNum, i, 1) = newchar If carry = 0 Then Exit For Next i End If "Francis Hookham" wrote: Many thanks Chip - no problem - the number of increments is unlikely to exceed half a dozen. D10-001 etc are door numbers in a schedule - the alpha suffix is only used when a door is lotted in later. I just wanted to be able to slot in a few more is necessary. If more than a few, total renumbering would be justified. I am most grateful Francis "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))) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
Francis: Chips code has a few problems. What if the last character is a
number? Both the formula and VBA versions properly handle numeric input. Both will increment 101 to 102 and 101-1 to 101-2. I left the 'z' case undefined because there are any number of things that might be the increment of 'z'. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Joel" wrote in message ... Francis: Chips code has a few problems. What if the last character is a number? If you have any problems try this code. It also handles the case when you get to z it will cause a carry and increment the next character. It also handles numbers and letters as last character If IsNumeric(sDNum) Then sDoorNum = Val(sDNum) 'add 1 to number sDoorNum = sDoorNum + 1 'format number so it contain leading zeros sDNum = Format(sDoorNum, "0##") Else carry = 1 stringlen = Len(sDNum) For i = stringlen To (stringlen - 2) Step -1 char = Mid(sDNum, 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(sDNum, i, 1) = newchar If carry = 0 Then Exit For Next i End If "Francis Hookham" wrote: Many thanks Chip - no problem - the number of increments is unlikely to exceed half a dozen. D10-001 etc are door numbers in a schedule - the alpha suffix is only used when a door is lotted in later. I just wanted to be able to slot in a few more is necessary. If more than a few, total renumbering would be justified. I am most grateful Francis "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))) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
Chip: what does your code do with "0D9". Does it make it "0D:" ?
"Chip Pearson" wrote: Francis: Chips code has a few problems. What if the last character is a number? Both the formula and VBA versions properly handle numeric input. Both will increment 101 to 102 and 101-1 to 101-2. I left the 'z' case undefined because there are any number of things that might be the increment of 'z'. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Joel" wrote in message ... Francis: Chips code has a few problems. What if the last character is a number? If you have any problems try this code. It also handles the case when you get to z it will cause a carry and increment the next character. It also handles numbers and letters as last character If IsNumeric(sDNum) Then sDoorNum = Val(sDNum) 'add 1 to number sDoorNum = sDoorNum + 1 'format number so it contain leading zeros sDNum = Format(sDoorNum, "0##") Else carry = 1 stringlen = Len(sDNum) For i = stringlen To (stringlen - 2) Step -1 char = Mid(sDNum, 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(sDNum, i, 1) = newchar If carry = 0 Then Exit For Next i End If "Francis Hookham" wrote: Many thanks Chip - no problem - the number of increments is unlikely to exceed half a dozen. D10-001 etc are door numbers in a schedule - the alpha suffix is only used when a door is lotted in later. I just wanted to be able to slot in a few more is necessary. If more than a few, total renumbering would be justified. I am most grateful Francis "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))) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
Now I see what you mean. You're right, the formula is flawed.
-- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Joel" wrote in message ... Chip: what does your code do with "0D9". Does it make it "0D:" ? "Chip Pearson" wrote: Francis: Chips code has a few problems. What if the last character is a number? Both the formula and VBA versions properly handle numeric input. Both will increment 101 to 102 and 101-1 to 101-2. I left the 'z' case undefined because there are any number of things that might be the increment of 'z'. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Joel" wrote in message ... Francis: Chips code has a few problems. What if the last character is a number? If you have any problems try this code. It also handles the case when you get to z it will cause a carry and increment the next character. It also handles numbers and letters as last character If IsNumeric(sDNum) Then sDoorNum = Val(sDNum) 'add 1 to number sDoorNum = sDoorNum + 1 'format number so it contain leading zeros sDNum = Format(sDoorNum, "0##") Else carry = 1 stringlen = Len(sDNum) For i = stringlen To (stringlen - 2) Step -1 char = Mid(sDNum, 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(sDNum, i, 1) = newchar If carry = 0 Then Exit For Next i End If "Francis Hookham" wrote: Many thanks Chip - no problem - the number of increments is unlikely to exceed half a dozen. D10-001 etc are door numbers in a schedule - the alpha suffix is only used when a door is lotted in later. I just wanted to be able to slot in a few more is necessary. If more than a few, total renumbering would be justified. I am most grateful Francis "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))) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
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))) |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
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))) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
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))) |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
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))) |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Incrementing an alpha character
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))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if alpha character | Excel Discussion (Misc queries) | |||
| test if a character in a string is alpha | Excel Programming | |||
| All possible Alpha character combonations | Excel Programming | |||
| Parsing a alpha character out of a cell | Excel Worksheet Functions | |||
| Increment Alpha Character | Excel Programming | |||
Linear Mode
