getting the column name
 

I need to put the column name in a variable when I stand on a cetain cell.
If I use activecell.column
I get the column number, but I need the name.
When I'm in cell C5 I want to put C in a variable (and not the columnindex
3), and when I'm in cell HD3 then I want to get HD instead of 212.

Can anyone help me with this?
I know I can get the whole adress from the activecell and then trim it but I
guess that there's an easier way.

getting the column name
 

"sbdproj" skrev i melding
...
When I'm in cell C5 I want to put C in a variable (and not the columnindex
3), and when I'm in cell HD3 then I want to get HD instead of 212.

Can anyone help me with this?
I know I can get the whole adress from the activecell and then trim it but
I
guess that there's an easier way.

I don't think so
=MID(ADDRESS(1,COLUMN()),2,1+(COLUMN()26))
but hopefully I'm wrong.
--
HTH. Best wishes Harald
Followup to newsgroup only please

getting the column name
 

One way:

ColumnName = Split(ActiveCell.Address, "$")(1)

The SPLIT function was first introduced in Excel 2000.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

"sbdproj" skrev i en meddelelse
...
I need to put the column name in a variable when I stand on a cetain cell.
If I use activecell.column
I get the column number, but I need the name.
When I'm in cell C5 I want to put C in a variable (and not the columnindex
3), and when I'm in cell HD3 then I want to get HD instead of 212.

Can anyone help me with this?
I know I can get the whole adress from the activecell and then trim it but
I
guess that there's an easier way.

getting the column name
 

Hej Harald

A bit shorter :-)

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")

--
Best Regards
Leo

"Harald Staff" skrev i en meddelelse
...
"sbdproj" skrev i melding
...
When I'm in cell C5 I want to put C in a variable (and not the
columnindex
3), and when I'm in cell HD3 then I want to get HD instead of 212.

Can anyone help me with this?
I know I can get the whole adress from the activecell and then trim it
but
I
guess that there's an easier way.

I don't think so
=MID(ADDRESS(1,COLUMN()),2,1+(COLUMN()26))
but hopefully I'm wrong.
--
HTH. Best wishes Harald
Followup to newsgroup only please

getting the column name
 

Neat :-)

Best wishes Harald
Followup to newsgroup only please

"Leo Heuser" skrev i melding
...
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")

getting the column name
 

I picked this up from another thread - Mr Ogilvy I think -

Public Function CLetter(rng As Range) As String
CLetter = Left(rng.Address(False, False), _
1 - CInt(rng.Column 26))
End Function

getting the column name
 

Pickedthis up from an earlier thread - Mr Ogilvy I think.

Works like a charm in 97

Public Function CLetter(rng As Range) As String
CLetter = Left(rng.Address(False, False), _
1 - CInt(rng.Column 26))
End Function