Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple calculation where values change to letters? | Excel Worksheet Functions | |||
Putting data from columns into rows | Excel Discussion (Misc queries) | |||
Unable to change field settings in calculated field in a pivot tab | Excel Discussion (Misc queries) | |||
Putting the Subject field in SendMail | Excel Programming | |||
Putting the Subject field in SendMail | Excel Programming |