address of cell formula is in

  • Thread starter Thread starter mark kubicki
  • Start date Start date
M

mark kubicki

how do i get the column number of the cell that my formula is written in

guess it's not: activecell.column
 
If your formula is a User defined function, use application.Caller

Public Function MyFunc()
set rng = Application.Caller
Myfunc = rng.column
End Function

as a trivial example.
 
I put this in a general module

Public Function MyFunc()
Set rng = Application.Caller
MyFunc = rng.Column
End Function

in the worksheet I put in

=myfunc()

and it returns the column of the cell containing the function which is then
displayed in the cell.


What are you doing?
 
gotcha...

and that's where i get the error??


Tom Ogilvy said:
I put this in a general module

Public Function MyFunc()
Set rng = Application.Caller
MyFunc = rng.Column
End Function

in the worksheet I put in

=myfunc()

and it returns the column of the cell containing the function which is then
displayed in the cell.


What are you doing?

--
Regards,
Tom Ogilvy

written
Click to expand...
 
Application.Caller returns a range (the range of the cell containing the
function) when called from a cell.

So It is hard to imagine how you could be getting this error using the same
code I provided in the same way I describe.
 
Back
Top