Get Address of Current Cell

[dhstein]

Below is a function that will return the column of a cell. The "CELL"
function will give a column number - I want the column name e.g. "AM". The
function works - sort of. The problem is if I have a cell with "=COL()" I
can't copy that formula to other cells. I think I'm using the wrong Address
(Selection.Address). Any advice is appreciated. Thanks.

Function Col(Optional Column As Integer)

' This Function is used to return the column of a cell - useful for
"INDIRECT" function
' Typical use is =col(CELL("COL",AM1)) - this would return the value "AM"
' Alternatively can be called as =col() which returns the column of the
current cell


Select Case Column

Case Is > 0
FC = Chr(Int((Column - 1) / 26) + 64)
SC = Chr(((Column - 1) Mod 26) + 65)
If Column < 27 Then
Col = SC
Else
Col = FC + SC
End If
Case Is = 0
Addr = Selection.Address
First_Dollar = Application.WorksheetFunction.Find("$", Addr, 1)
Second_Dollar = Application.WorksheetFunction.Find("$", Addr,
First_Dollar + 1)

Col = Mid$(Addr, 2, (Second_Dollar - First_Dollar) - 1)
End Select
End Function

 

[Gary''s Student]

Function colid() As String
v = Application.Caller.Address
colid = Split(v, "$")(1)
End Function

This gives the column ID of the cell containing the formula. (part of your
requirement)

 

[Rick Rothstein \(MVP - VB\)]

Can you make use of this worksheet formula instead?

=SUBSTITUTE(ADDRESS(1,COLUMN(AM2),4),"1","")

You would put in the cell reference in the COLUMN function, or leave it
empty, and it will give you that cell's column ID in letters.

Rick

 

[Dave Peterson]

I don't know what you're using in the =indirect(), but that has a parm that
allows you to use R1C1 reference style, too.

 

[dhstein]

Thanks Gary Rick and Dave. By replacing Addr = Selection.Address with
Addr = Application.Caller.Address it works fine. (thanks Gary). Gary -
your code is much more efficient than mine for the Case = 0 option. Dave -
thanks for the info on the INDIRECT function. I sometimes build an address
by concatenating a row and column e.g A1 might equal "BC" and A2 might
equal 14 - so I use =INDIRECT(A1&A2) which gives me the value in cell BC14.
I'm not sure if the other method of INDIRECT will help with that, which is
why I need the function to return "BC" instead of 55.

 

[Dave Peterson]

if A1 contains BC and A2 contains 14, then =indirect(a1&a2) will return what's
in BC14

If A1 contains 55 and a2 contains 14, then =indirect("r"&a2&"c"&a1,false) will
return what's in BC14. There is no need to convert 55 to BC.

It's like writing:
=R14C55
when you're using R1C1 reference style.

 

[Gary''s Student]

You are very welcome!

 

[emel]

Thanks - I'd been puzzled about how to find the cell that called a macro. I
had reluctantly resorted to passing the calling cell as a parameter.

Application.caller is much better.

Ed