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


"Jean" wrote:

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

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(A10,A1<257),IF(A126,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/Snippe...and_Names.html


VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=564070

"Jean" skrev i en meddelelse
...
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



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.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

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
| www.XLsupport.com | keepITcool chello nl | amsterdam


Leo Heuser wrote in

"Jean" skrev i en meddelelse
...
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



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.

"keepITcool" skrev i en meddelelse
.com...
Leo,

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


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" wrote in message
...
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


"Jean" wrote:

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