getting the column name

[sbdproj]

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.

 

[Harald Staff]

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.

 

[Leo Heuser]

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.

 

[Leo Heuser]

Hej Harald

A bit shorter

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

 

[Harald Staff]

Neat

Best wishes Harald
Followup to newsgroup only please

 

[Alan Webb]

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

 

[Alan Webb]

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