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

www.raising-cain.com

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

--
Regards,
Tom Ogilvy


"Lee Cain" wrote in message
...
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

www.raising-cain.com

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


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Lee Cain" wrote in message
...
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

www.raising-cain.com

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


--

HTH

Bob Phillips

"Lee Cain" wrote in message
...
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

www.raising-cain.com

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


--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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


--

HTH

Bob Phillips

"Lee Cain" wrote in message
...
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

www.raising-cain.com

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.

--

HTH

Bob Phillips

"Tom Ogilvy" wrote in message
...
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


--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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


--

HTH

Bob Phillips

"Lee Cain" wrote in message
...
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

www.raising-cain.com

Fabulous!
All I need is 1 & 2 beyond what ever my source letter is!
No fear of Z here.

Thanks!
--
Lee

www.raising-cain.com
"Bob Phillips" wrote in message
...
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


--

HTH

Bob Phillips

"Lee Cain" wrote in message
...
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

www.raising-cain.com