Return activecell's column header in Alphabet

[Guest]

Pls teach me the correct way to "return activecell's column header in
Alphabet".

In VBA:
Columns(1) represents column A
Columns(2) represents column B
Columns(5) represents column E
Columns(26) represents column Z

When I get VBA to return a variable (an integer) between 1~26, how can I
convert this back to alphabet (A~Z)?

Currently in my codes, I do like this to display the “Alphabet†of the
column header of activecell:
'Msgbox Left(ActiveCell.Address(False, False, xlA1),1)

Now, I get into trouble when the returned integer exceeds 26.

Thanks a lot.

 

[Tim Williams]

dim x as integer, sLetter as string

x=28
sLetter = replace(activesheet.cells(1,x).address(false,false),"1","")

Tim

 

[Guest]

Try:

?application.Columns(activecell.Column).address
$AQ:$AQ

?application.Columns(activecell.Column).address
$BH:$BH

Extract the string before the coluon and lose the $ sign.

There must be a cleaner way!

 

[Guest]

Dear Tim,

Thank you for giving me the good start.

My final code went like this:
Dim x As Integer, sLetter As String, y As Integer

y = ActiveCell.Row
x = ActiveCell.Column
Debug.Print sLetter = Replace(ActiveSheet.Cells(y, x).Address(False, False),
y, "")

--
Edmund
(Using Excel XP)

dim x as integer, sLetter as string

x=28
sLetter = replace(activesheet.cells(1,x).address(false,false),"1","")

Tim

 

[Per Jessen]

Hi Rob

Here's a way to do it.

Dim strLastColumn As String
Dim L As Integer

strLastColumn = ActiveCell.Address
L = InStr(2, strLastColumn, "$", 1)
strLastColumn = Mid(strLastColumn, 2, L - 2)

Regards,
Per

Tim,

Is there a way to get Excel to return the column in letter form? I am
trying to extract the alphabetical reference to a cell but I keep getting
the
number. I use code like this:

Dim strLastColumn as String

strLastColumn = ActiveCell.Column

The .Column property always returns the number. So, if I am in column Z
it
returns 26 instead of Z. I need to return Z.

Thanks,

Rob