I have a spreadsheet that I routinely add and delete rows in, some of the cell functions in those rows refer to the row above to do calculations. These cells end up with the wrong values when you insert rows (the row below now refers to the one 2 above) and deleting rows produces lots of #ref's.
I originally fixed it by replacing the reference to the cell above with =indirect(address(row()-1,column(),4)).
This works fine, and calculates properly when adding or deleting rows from the spreadsheet, though I'd prefer a simple VBA function. So I wrote (simplified):
Function Ofst(R As Long, C As Long, Addr As Variant ) As Variant
Ofst = Cells(Addr.Row + R, Addr.Column + C).Value
End Function
So I can replace [A2]= a1+1 with [A2]=Ofst(A2,-1,0)+1
And this also works ok, but I'd like to drop the A2 from within Ofst() and do
Function Ofst(R As Long, C As Long, optional Addr As Variant) As Variant
If IsMissing(Addr) Then
Ofst = Cells(?.Row + R, ?.Column + C).Value
Else
Ofst = Cells(Addr.Row + R, Addr.Column + C).Value
End If
End Function
Which works fine when I specify the address to offset from, but what do I replace the '?'s with, how do you refer to the current cell that the function is being called from in a VBA UDF?
I'd like this because I have some cells where I want [A2]=Ofst(-1,0)+Ofst(-1,0,X2), so I'd have one function that can either give the value in the cell above (or whatever the Row Column offsets given point to) the current one if the 3rd parameter isn't specified, or above the specified cell if it is.
It's NOT ActiveCell, because replacing ? in that function with ActiveCell, makes all the cells this function is in refer to whichever cell the cursor is in.
Can anyone point me in the right direction?
I originally fixed it by replacing the reference to the cell above with =indirect(address(row()-1,column(),4)).
This works fine, and calculates properly when adding or deleting rows from the spreadsheet, though I'd prefer a simple VBA function. So I wrote (simplified):
Function Ofst(R As Long, C As Long, Addr As Variant ) As Variant
Ofst = Cells(Addr.Row + R, Addr.Column + C).Value
End Function
So I can replace [A2]= a1+1 with [A2]=Ofst(A2,-1,0)+1
And this also works ok, but I'd like to drop the A2 from within Ofst() and do
Function Ofst(R As Long, C As Long, optional Addr As Variant) As Variant
If IsMissing(Addr) Then
Ofst = Cells(?.Row + R, ?.Column + C).Value
Else
Ofst = Cells(Addr.Row + R, Addr.Column + C).Value
End If
End Function
Which works fine when I specify the address to offset from, but what do I replace the '?'s with, how do you refer to the current cell that the function is being called from in a VBA UDF?
I'd like this because I have some cells where I want [A2]=Ofst(-1,0)+Ofst(-1,0,X2), so I'd have one function that can either give the value in the cell above (or whatever the Row Column offsets given point to) the current one if the 3rd parameter isn't specified, or above the specified cell if it is.
It's NOT ActiveCell, because replacing ? in that function with ActiveCell, makes all the cells this function is in refer to whichever cell the cursor is in.
Can anyone point me in the right direction?
Last edited: