putting a simple calculation in a field that may change rows
 

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:

CODE
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


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=483141

putting a simple calculation in a field that may change rows
 

ActiveCell.FormulaR1C1 = "=R2C-R[-2]C"

--
Regards,
Tom Ogilvy

"matpj" wrote in
message ...

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:

CODE
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


--
matpj
------------------------------------------------------------------------
matpj's Profile:
http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=483141