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!

Hi manish -

The code below will increment the index letters in your variable "char" up
to "ZZ", the maximum for a two-letter index:

Select Case Len(char)

Case 1 'char is one letter
If UCase(char) = "Z" Then
CellOfInterest.Value = "AA"
Else
CellOfInterest.Value = Chr(Asc(char) + 1)
End If

Case 2 'char is two letters
If Right(char, 1) = "Z" Then
CellOfInterest.Value = Chr(Asc(Left(char, 1)) + 1) & "A"
Else
CellOfInterest.Value = Left(char, 1) & Chr(Asc(Right(char, 1)) +
1)
End If

End Select

----
Jay


"manish" wrote:

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!

Hello,

If you like to use a UDF:

Function charinc(s As String) As String
'Increments a string:
'A will become B
'Z will become AA
'ABCZ will become ABDA
'ZZZ will become AAAA
'Reverse(moc.liborplus.www) v0.10
Dim i As Long
Dim sc As String 'Current char
Dim sp As String 'Prefix
Dim ss As String 'Suffix

sp = s
ss = ""
i = Len(s)
Do While i 0
sc = Right(sp, 1)
If i 1 Then
sp = Left(sp, i - 1)
Else
sp = ""
End If
Select Case sc
Case "A" To "Y"
sc = Chr(Asc(sc) + 1)
Exit Do
Case "Z"
ss = "A" & ss
If i = 1 Then
sp = "A"
sc = ""
Exit Do
End If
Case Else
charinc = CVErr(xlErrValue)
Exit Function
End Select
i = i - 1
Loop
charinc = sp & sc & ss
End Function

Regards,
Bernd

On Nov 4, 1:47 am, Bernd P wrote:
Hello,

If you like to use a UDF:

Function charinc(s As String) As String
'Increments a string:
'A will become B
'Z will become AA
'ABCZ will become ABDA
'ZZZ will become AAAA
'Reverse(moc.liborplus.www) v0.10
Dim i As Long
Dim sc As String 'Current char
Dim sp As String 'Prefix
Dim ss As String 'Suffix

sp = s
ss = ""
i = Len(s)
Do While i 0
sc = Right(sp, 1)
If i 1 Then
sp = Left(sp, i - 1)
Else
sp = ""
End If
Select Case sc
Case "A" To "Y"
sc = Chr(Asc(sc) + 1)
Exit Do
Case "Z"
ss = "A" & ss
If i = 1 Then
sp = "A"
sc = ""
Exit Do
End If
Case Else
charinc = CVErr(xlErrValue)
Exit Function
End Select
i = i - 1
Loop
charinc = sp & sc & ss
End Function

Regards,
Bernd

Thanks a lot. It really works!

Hi

Whilst you have been given answers to the question you posed, if the object
is to make up a range value to extract data from a cell e.g.
Range("AA1").value, you might find it easier to use Cells()

x= x+1 (where x was 26)
Cells(1,x).value would give the same result.

--
Regards
Roger Govier



"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!

'On my XL97, this routine goes up to column "IV"

Sub Demo()
MsgBox ColumnPlusOne("Z")
MsgBox ColumnPlusOne("IU")
End Sub

Function ColumnPlusOne$(pStr$)
Dim s1$
s1 = Columns(Columns(pStr).Column + 1).Address
ColumnPlusOne = Mid$(s1, InStr(s1, ":$") + 2)
End Function ' D-C Dave


manish wrote:
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!


----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

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!

Hello Dana,

Your macro takes 50% more runtime than mine (FastExcel says) and it
does not work for ZZZZZZZZZZZZ or longer strings. But maybe we should
even restrict our solutions to IV :-)

Regards,
Bernd

Hi Bernd. Thanks for the catch. You're right. I see the logic error.
Most of the time, the last character is not "z." Your code logically exits
the function much earlier. My code unnecessarily keeps working. :~

This code is not any better, and may be slower than your excellent code, but
was something I was messing around with.

Function IncChar(Str) As String
Dim s As String
Dim C As String 'Character
Dim p As Long ' Loop Counter
Dim CF As Boolean 'Carry Flag
Const Z As String = "Z"
Const A As String = "A"

s = UCase(Str)
If Not s Like WorksheetFunction.Rept("[A-Z]", Len(s)) Then
IncChar = "#N/A"
Exit Function
End If

'// Most Common - does not end in Z
If Not (s Like "*Z") Then
Mid(s, Len(s), 1) = Chr(Asc(Right$(s, 1)) + 1)
Else
CF = True
For p = Len(s) To 1 Step -1
C = Mid$(s, p, 1)
If C = Z Then
Mid(s, p, 1) = A
Else
If Not CF Then Exit For
C = Chr(Asc(C) + 1)
Mid(s, p, 1) = C
End If
CF = (C = Z)
Next p
If CF Then s = A & s
End If
IncChar = s
End Function

--
Again, thanks for the catch.
Dana DeLouis


"Bernd P" wrote in message
ps.com...
Hello Dana,

Your macro takes 50% more runtime than mine (FastExcel says) and it
does not work for ZZZZZZZZZZZZ or longer strings. But maybe we should
even restrict our solutions to IV :-)

Regards,
Bernd

Hello Dana,

Now your code is about 10% faster than mine.

Have fun,
Bernd