current row of user-defined function?

[usenethelp]

Is it possible to get the row that a user-defined function is on?
ActiveCell.Row seems work, until you drag down to fill the function at
which time each funcion will return only the first cell (the
activecell). I think I need this in order to reference other cells in
the same row that the function is in.

 

[Norman Jones]

Hi Usenethelp,

Try:

Application.Caller.Row

 

[Bernie Deitrick]

Use Net Help,

In your function, use:

Dim myRow As Long
Dim myVal As Variant

myRow = Application.Caller.Row
'To get data from column A
myVal = Application.Caller.Parent.Cells(myRow, 1).Value

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

set rng = Application.Caller

will give a range reference to the cell containing the UDF

rng.row

would give you the row.

 

[Charles Williams]

Dont forget that if your UDF references other cells like this

I think I need this in order to reference other cells in

that you will need to make it volatile ...

Application.volatile true

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[usenethelp]

Thanks guys!!