how to get from row,col to cell coordinate? 1

[BlackSeal]

I've been the net for hours now before posting this questions. The question seems trival, but here we go:

How can I get from a selected or activated cell it's coordinates in the form <characters><number> e.g. "QA23" I does not seem to be in the properties of the Cell, I know one can convert the number 3 into the character 'C' but I was hoping excell as built-in support for it, but I can't seem to find it. thanks for your help.

Alwyn

 

[satchmo]

In VBA you can use:

Excel.Activecell.Address

This will give you the cell in the form of $(column)$(row), or $QA$23, for example.

Is that what you are looking for?

 

[BlackSeal]

Thanks that was just it, but it is not in the default properties-list of an active cell that is wy I could find it. problem solved just int ime to start the weekend happy.

 

[bltseattle]

In an excel worksheet you can use the formula

=CELL("ADDRESS",qa23)

As an aside I find CELL("FILENAME",a1) to be very useful, also

 

[nhebb]

This is one of those annoyances that I ended up writing a function for. I always figured there should be a built-in way to get the alpha reference for the column, but I never could find one. Anyway, here's the function I wrote:

Public Function GetColumnAlpha(ByRef cell As Range) As String
    
    Dim s       As String
    Dim arr     As Variant
    
    s = cell.AddressLocal(True, False)
    arr = Split(s, "$")
    GetColumnAlpha = arr(0)
    
End Function

The AddressLocal() leaves the SheetName! out of the result. The True and False parameters are for whether you want absolute column and row references (i.e., the dollar $ sign in the address). I leave the absolute reference for the row so I can use it as a split delimeter.

HTH,

Nick Hebb