putting a simple calculation in a field that may change rows

[t16turbo]

I have the followingf code to put a formula in a cell.

THis code finds where that cell is, but the formula will always refer to the value in the same column in the second row, MINUS the value in the same column but two rows above the formula.
with the macro recorder I get the following:

Range("B:B").Select
  Selection.Find(What:="Total Surplus", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(0, 2).Activate
ActiveCell.FormulaR1C1 = "=R[-62]C-R[-2]C"

its the R[-62]C and R[[-2]C that I need to make intelligent.

the 62 component needs to be a value frmo the second row

and obviously the -2 bit refers to whatever is in the cell 2 cells ABOVE the formula.


i'm not sure how to manipulate the FormulaR1C1 property to get the desired results.
thanks in advance

 

[Zathras]

You probably want "=R2C-R[-2]C"

One way to handle the conversion yourself is to enter the formula manually and then switch the display to R1C1 style (Tools/Options.../General R1C1 reference style) and look at the formula that way.