How to determine the column letter from Cell address

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(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/Snippe...and_Names.html


VBA Noob

"Jean" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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 <news:<[email protected]>

> "Jean" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > 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" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >