getting the column name

  • Thread starter Thread starter sbdproj
  • Start date Start date
S

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.
 
sbdproj said:
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.
Click to expand...

I don't think so
=MID(ADDRESS(1,COLUMN()),2,1+(COLUMN()>26))
but hopefully I'm wrong.
 
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.
 
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
 
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
 
Back
Top