Setting a code range = coords of the cell containing the function

[Keith Howard]

Prefatory note: I apologize if this is a duplicate question. I received a
response to a question I posted around 1-2 weeks ago, but I was unable to
open the link for some reason, after many attempts. If my below question has
already been answered, please just refer to the answer. Thanks.

Now, the question: I need to set a range in code. The range needs to equal
the coordinates of the cell containing the function call. I would then copy
that function call to many cells. Each such function call would identify its
coordinates, in a relative, intelligent way, e.g. using the Row() and
Column() function calls, embedded within the call to the code function that I
am trying to write.

In any case, the function I am trying to write would receive an X and a Y
coordinate and would then set a range in code to equal such coordinates. (The
range should only be 1 x 1.) I would then use the .Offset function to
undertake various (and irrelevant, for the purpose of this question)
operations.

I do not want to change the currently highlighted cell (so I think I do not
want to use the .Select? method in code).

Does that make sense?

Regards,

Keith

 

[Mike H]

Keith,

It's not clear what you actually want but if you want the address from where
a function is called there are a couple of ways

1.

Function MyFunc()
CallerAddress = Application.Caller.Worksheet.Name & _
"!" & Application.Caller.Address
MsgBox CallerAddress
End Function

or

2.

sht=Activesheet.name
rRow=activecell.row
rCol=activecell.column

Mike