How to determine the column letter from Cell address

  • Thread starter Thread starter Jean
  • Start date Start date
J

Jean

Hi,

If there a function in VBA that return the column letter from a given cell
address ? For example, I need to know the that the column letter for
Cell(7,1) is G.

Thanks

JH
 
Since the column letter can be AA, AB etc. it's not entirely simple. This
should do:

Sub Test()
MsgBox GetColLetter(ActiveCell)
End Sub

Function GetColLetter(c As Range) As String
Dim txt As String
txt = c.EntireColumn.Address(0, 0)
GetColLetter = Left(txt, InStr(txt, ":") - 1)
End Function

Regards,
Greg
 
Hi

Not sure if any of these are what your after. The 7 in your reference =
G. Put this formula into B1. Enter 7 and it will return G

=IF(AND(A1>0,A1<257),IF(A1>26,CHAR(64+INT((A1-1)/26)),"")&CHAR(65+MOD(A1-1,26)),"")

Or if you want to see headers as a number instead of letters go to
options by


Tools, Options, General, R1C1 Refence style. This changes the letters
to numbers

or if your after VBA code to select active cell

MyColumnNumber = ActiveCell.Column


http://www.vba-programmer.com/Snippets/Code_Excel/Column_Numbers_and_Names.html


VBA Noob
 
Jean said:
Hi,

If there a function in VBA that return the column letter from a given cell
address ? For example, I need to know the that the column letter for
Cell(7,1) is G.

Thanks

JH
Click to expand...

Hi Jean

One way for Excel 2000 and on:

Function GetColumn(Cell As Range)
'Leo Heuser, 23.7.2006
GetColumn = Split(Cell.Address, "$")(1)
End Function


Sub test()
MsgBox GetColumn(Cells(1, 7))
End Sub


An example of a worksheet formula would be:

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

Just replace 7 by the column number.
 
Leo,

following works in all excel versions and is even
a bit (30%) faster than your elegant split trick

Function ColumnLetter(ByVal c As Range) As String
Dim i&
i = c.Column
'Fast and Office12 ready
Select Case i
Case 1 To 26
ColumnLetter = Chr$(64 + i)
Case 27 To 702
ColumnLetter = Chr$(64 + (i - 1) \ 26) & Chr$(65 + (i - 1) Mod 26)
Case 703 To 16384
ColumnLetter = Chr$(64 + (i - 1) \ 676)
i = 1 + ((i - 1) Mod 676)
ColumnLetter = ColumnLetter & Chr$(64 + (i - 1) \ 26) & Chr$(65 +
(i - 1) Mod 26)
End Select
End Function
 
keepITcool said:
Leo,

following works in all excel versions and is even
a bit (30%) faster than your elegant split trick
Click to expand...

Wow, 0.7 nanosecs instead of 1 :-)

Elegant, yes, but unfortunately not mine.
I'm not sure, but I believe it was conjured up
by Dana DeLouis years ago.

Leo
 
Thanks everyone.

Greg Wilson said:
Since the column letter can be AA, AB etc. it's not entirely simple. This
should do:

Sub Test()
MsgBox GetColLetter(ActiveCell)
End Sub

Function GetColLetter(c As Range) As String
Dim txt As String
txt = c.EntireColumn.Address(0, 0)
GetColLetter = Left(txt, InStr(txt, ":") - 1)
End Function

Regards,
Greg
Click to expand...
 
Back
Top