Return activecell's column header in Alphabet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Edmund - 12 May 2006 05:18 GMT

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.

Signature

Edmund
(Using Excel XP)

Reply to this Message

Tim Williams - 12 May 2006 06:32 GMT

dim x as integer, sLetter as string

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

Tim

> Pls teach me the correct way to "return activecell's column header in
> Alphabet".
[quoted text clipped - 15 lines]
>
> Thanks a lot.

Reply to this Message

Edmund - 12 May 2006 07:09 GMT

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, "")

Signature

Edmund
(Using Excel XP)

> dim x as integer, sLetter as string
>
[quoted text clipped - 22 lines]
> >
> > Thanks a lot.

Reply to this Message

Rob - 14 May 2008 03:46 GMT

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

> dim x as integer, sLetter as string
>
[quoted text clipped - 22 lines]
> >
> > Thanks a lot.

Reply to this Message

Per Jessen - 14 May 2008 07:54 GMT

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,
>
[quoted text clipped - 42 lines]
>> >
>> > Thanks a lot.

Reply to this Message

AA2e72E - 12 May 2006 06:33 GMT

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!

Reply to this Message

Rate this thread: