G
Guest
Below I have a function calculates the difference of 2 values based on a cell reference and an interval
For example: =ROC(A10,3) calculates the difference between A10 - A8 (which is what I'm after).....
Function ROC(val1 As Range, per As Integer
Set val2 = Cells(Range(val1.Address).Row - per + 1, Range(val1.Address).Column
If Range(val1.Address).Row - per < 1 The
R0C = "
Els
ROC = (val1 - val2
End I
End Functio
There are 2 problems with the above code
1) When the formula is first entered it calculates correctly, however when the 2nd parameter is referred to by reference the formula does not recalculate correctly when the value in this cell is changed. It infact shows the value of val1....I have checked the options and calculation is set to automatic and iterations are on and to 100..
2) It seems quite slow to run. Can it be optimised in anyway
Any help would be appreciate
Ma
For example: =ROC(A10,3) calculates the difference between A10 - A8 (which is what I'm after).....
Function ROC(val1 As Range, per As Integer
Set val2 = Cells(Range(val1.Address).Row - per + 1, Range(val1.Address).Column
If Range(val1.Address).Row - per < 1 The
R0C = "
Els
ROC = (val1 - val2
End I
End Functio
There are 2 problems with the above code
1) When the formula is first entered it calculates correctly, however when the 2nd parameter is referred to by reference the formula does not recalculate correctly when the value in this cell is changed. It infact shows the value of val1....I have checked the options and calculation is set to automatic and iterations are on and to 100..
2) It seems quite slow to run. Can it be optimised in anyway
Any help would be appreciate
Ma