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

  • Thread starter Thread starter Keith Howard
  • Start date Start date
K

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
 
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
 
Back
Top