Increase letter by one

  • Thread starter Thread starter Lee Cain
  • Start date Start date
L

Lee Cain

I have a string variable Col = "A"
I want to be able to use Col +1 as "B"
What's the correct code?

This must be simple??

Thanks,
 
Lee,

Try,

Col = Chr(Asc(Col)+1)

Won't work beyond Z of course. For that I suggest holding the letter index
in a long variable and incrementing this, generating the letter from it

If icol > 26 Then
col = Chr(icol \ 26 + 64) & Chr(icol Mod 26 + 64)
Else
col = Chr(icol + 64)
End If
 
chr(Asc(col)+1)

demo'd from the immediate window:

col = "A"
? chr(Asc(col)+1)
B


or
a bit more robust:

col = "A"
? left(columns(col).Offset(0,1).Address(0,0),2 + (columns(col).column < 27))
B
col = "AA"
? left(columns(col).Offset(0,1).Address(0,0),2 + (columns(col).column < 27))
AB
 
Whoops - slight error in the second formula - fails at Z.

Here is the adjustment:

col = "Z"
? left(columns(col).Offset(0,1).Address(0,0),2 + (columns(col).column+1 <
27))
AA
 
Here is Bob's excellent solution setup as a function:

Public Function Add1Col(sStr)
icol = Columns(sStr).Column + 1
If icol > 26 Then
col = Chr(icol \ 26 + 64) & Chr(icol Mod 26 + 64)
Else
col = Chr(icol + 64)
End If
Add1Col = col
End Function


demo'd in the immediate window:

? add1col("AA")
AB
? add1col("Z")
AA

? add1col("B")
C
 
Fabulous!
All I need is 1 & 2 beyond what ever my source letter is!
No fear of Z here.

Thanks!
 
Well that's funny, 'cos I liked yours, the
left(columns(col).Offset(0,1).Address(0,0),2 + (columns(col).column+1 <27))
version that is, using the worksheet to work through the letter series,
very neat.
 
Back
Top