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

"Rob" skrev i meddelelsen
...
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

"Tim Williams" wrote:

dim x as integer, sLetter as string

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

Tim


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

--
Edmund
(Using Excel XP)