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.