ActiveCell in Excel Trouble - Multiple References

D-Generate

Evening all,

I am currently doing up a spreadsheet to do some data processing and as part of the data processing I am applying filters to the data. The windows of the filters can have variable sizes of say 'n' so I thought it would be best to create a userdefined function of the format "Function UserDefFun(n As Integer)".

Within my code I access the save the ActiveCell.Row, then I cycle back through "n" rows as part of the windowing process using the offset function. Unfortunately although this works for a single cell, when I place the same function in multiple cells it fails.

What appears to be happening is that there is a conflict in using ActiveCell.Row many times and as such depending on where I place the cursor when doing "Recalculate All" I get a different answer each time.
I would be grateful if anyone has an solution to this problem or can point me in the right direction.

Function GMAUDF(n_days As Integer, alph As Single)
    '
    ' Geometric Moving Average
    '
    Application.Volatile (True)
    Dim tally As Single
    Dim tot As Single
    Dim interm As Integer
    Dim i As Integer
    Dim actrow As Integer
    tally = 0
    tot = 0
    actrow = ActiveCell.Row
    
    For i = 1 To n_days
        interm = -1 * i
        tally = tally + (alph ^ (n_days - i)) * Cells(actrow, 6).Offset(interm, 0)
        tot = tot + alph ^ (n_days - i)
    Next i
    
    GMAUDF = tally / tot
End Function

D-Generate

And now i remember why it is sometimes best to leave a problem and go back to it.

I fixed it by just taking the lazy option and throwing in another argument and having that argument being the cell I want to reference for the offsetting.

Thanks all!