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)

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)

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)


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

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)

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)

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!