How to determine the column letter from Cell address

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
 
G

Greg Wilson

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
 
V

VBA Noob

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
 
L

Leo Heuser

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.
 
K

keepITcool

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
 
L

Leo Heuser

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
 
J

Jean

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...
 
Top