Column() convert to letter (A1 notation)

[John Kauffman]

Is there a way to get the column letter (in A1 notation) of the active cell, as opposed to its number (from R1C1 notation).

=ADDRESS(5,10,4 ) returns row and col in A1 notation, but getting just col is a parse job

My sources of column number (R1C1)
Formula: =Column()
Code: MsgBOx("active column is " & activecell.column())

 

[Scott R]

MsgBOx("active column is " & activecell.address)

 

[John Kauffman]

Scott:
Thanks for quick response.

The question is how to get only the column address in A1 notation.
I'm comfortable getting the entire address with either:
Activecell.address
or
Address(...)

Thanks, John

 

[Ver101]

Is there a way to get the column letter (in A1 notation) of the active cell, as opposed to its number (from R1C1 notation).

=ADDRESS(5,10,4 ) returns row and col in A1 notation, but getting just col is a parse job

My sources of column number (R1C1)
Formula: =Column()
Code: MsgBOx("active column is " & activecell.column())

You can use the Cell function ("address") changing all (find and replace function) except the A1 notation

 

[Ver101]

You can use the Cell function ("address") changing all (find and replace function) except the A1 notation

You can also used Address function removing excess through find and replace

 

[JoeMo]

Try this:

Sub GetColAdr()
Dim Adr As String, cAdr As String
With ActiveCell
    Adr = .Address
    For i = 1 To Len(Adr)
        If Not IsNumeric(Mid(Adr, i, 1)) Then cAdr = cAdr & Mid(Adr, i, 1)
    Next i
End With
MsgBox "Activecell is in column " & Mid(cAdr, 2, Len(cAdr) - 2)
End Sub

 

[pgc01]

Hi John

You can get the column in A1 notation, but why do you want to do it?

MsgBox Split(ActiveCell.Address, "$")(1)

 

[John Kauffman]

Thanks to both.
MoJoe - I was headed down your route; thanks for working code to which I can compare my parsing / conversion.
Pgc - brilliant & you introduced me to a whole new function

Will be used to report to user the col that has changed.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

Will be used to report to user the col that has changed.

I was wondering why you needed it as it is not needed in vba. Thanks.