address of cell formula is in

[mark kubicki]

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

guess it's not: activecell.column

 

[Tom Ogilvy]

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.

 

[mark kubicki]

i keep getting the error:

run-time 13, type mismatch??

 

[Tom Ogilvy]

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?

 

[mark kubicki]

gotcha...

and that's where i get the error??

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

 

[Tom Ogilvy]

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.