current row of user-defined function?

U

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.
 
B

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
 
T

Tom Ogilvy

set rng = Application.Caller

will give a range reference to the cell containing the UDF

rng.row

would give you the row.
 
C

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
Click to expand...

that you will need to make it volatile ...

Application.volatile true

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