Increment Letter in a String
If it is "AA", it should change to "AB" etc
I see you have solutions. If you would like a base-26 type of solution,
here are a few ideas:
Sub TestIt()
Debug.Print NextSequence("ZZZZZ")
Debug.Print NextSequence("ABCDEFGZ")
End Sub
Returns:
AAAAAA
ABCDEFHA
Function NextSequence(Seq As String) As String
'// Dana DeLouis
Dim p As Long
Dim k As Long
Dim x As Long
Dim s As String
Dim sol As String
Dim n As Double
Const B As Long = 26 'Base 26
s = UCase(Seq)
If Not bAllLetters(s) Then
NextSequence = "Error: Not all numbers: " & Seq
Exit Function
End If
k = Len(s)
For p = 1 To k
x = Asc(Mid$(s, k - p + 1, 1)) - 64
n = n + x * B ^ (p - 1)
Next p
n = n + 1 'Next Sequence
p = Int(Log(n) / Log(B))
Do While p = 0
x = Int(n / 26 ^ p)
sol = sol & Chr(x + 64)
n = n - x * B ^ p
p = p - 1
Loop
NextSequence = sol
End Function
Private Function bAllLetters(s As String) As Boolean
Const p As String = "[A-Z]"
bAllLetters = UCase(s) Like WorksheetFunction.Rept(p, Len(s))
End Function
--
HTH :)
Dana DeLouis
Windows XP & Excel 2007
"manish" wrote in message
oups.com...
Hi,
I am sorry if it is repost. I am relatively new to macros. I want to
increment letter in a string present in a cell. If the cell value is
"A" I want to make it "B" etc. However, if the cell value is "Z", I
want to change it to "AA". If it is "AA", it should change to "AB" and
so on. I wrote following code for increment letters:
NextIndex = Asc(char) + 1
CellOfInterest.Value = Chr(NextIndex)
Above code changes A to B, B to C and so on. However, it does not
change Z to AA. It just gives me next ASCII value ([). Is there any
way to make this happen?
Any help would be greatly appreciated!
|