How do I get the Function row and not the active row?

[USAMax]

I wrote a function that works when I enter it one row at a time because it uses the ActiveCell.row function. But when I copy that function down all of the results are looking at the ActiveCell's row and not the row the function is on. Here is the command, how do I change it?

    CurRow = ActiveCell.Row
        If Cells(CurRow, q) = MaxVal Then
            MaxMinRevenueYears = Right(Cells(HeaderRow - 1, q), 4)
            Exit For
        End If

 

[USAMax]

An example of my problem is, I copy the formula is in cell S5 and paste it down to S100.

The results in the pasted rows show the results for row 5 as it is the active row.

 

[Rick Rothstein]

I wrote a function that works when I enter it one row at a time because it uses the ActiveCell.row function. But when I copy that function down all of the results are looking at the ActiveCell's row and not the row the function is on. Here is the command, how do I change it?

    CurRow = ActiveCell.Row
        If Cells(CurRow, q) = MaxVal Then
            MaxMinRevenueYears = Right(Cells(HeaderRow - 1, q), 4)
            Exit For
        End If

Try this instead...

CurRow = Application.Caller.Row

 

[USAMax]

Rick, you always come through!

Thank you...