Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula R1C1 in VBA - Strange Results (Dynamic)
Hi,
I have the following code in VBA to give me some variance % results. Although the rows are always static the columns are not: ActiveCell.FormulaR1C1 = "=SUM(R[-34]C-R[-17]C)/R[-17]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-32]C-R[-15]C)/R[-15]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-30]C-R[-13]C)/R[-13]C" What I want this to do is to start in row 45, in the column i am in (worked out prior to this in the macro) and put in a formula which basically in Excel would read: =SUM(I11-I28)/I28 then down one row =SUM(I13-I30)/I30 then down one row =SUM(I15-I32)/I32 This works, but only for the 1st and 3rd formulae, the second one is always wrong by one row... Am I doing something majorly wrong here? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula R1C1 in VBA - Strange Results (Dynamic)
are you sure the 3rd formula is OK
All three of these formuls seem to be set up to reference the desired ranges from row 45 45-34=11 45-17=28 Good for row 45 and would be SUM(I11-I28)/I28 45-32=13 45-15=30 Good for row 45 and would be SUM(I13-I30)/I3 To put the formula in Row 46 you need to change the math a little 46-33=13 46-16=30, so your formula for would need to read "=SUM(R[-33]C-R[-16]C)/R[-16]C" The same logic would follow for the 3rd formula which is intended to go in row 47 -- If this helps, please remember to click yes. "Louise" wrote: Hi, I have the following code in VBA to give me some variance % results. Although the rows are always static the columns are not: ActiveCell.FormulaR1C1 = "=SUM(R[-34]C-R[-17]C)/R[-17]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-32]C-R[-15]C)/R[-15]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-30]C-R[-13]C)/R[-13]C" What I want this to do is to start in row 45, in the column i am in (worked out prior to this in the macro) and put in a formula which basically in Excel would read: =SUM(I11-I28)/I28 then down one row =SUM(I13-I30)/I30 then down one row =SUM(I15-I32)/I32 This works, but only for the 1st and 3rd formulae, the second one is always wrong by one row... Am I doing something majorly wrong here? Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula R1C1 in VBA - Strange Results (Dynamic)
Great thank you, I did have the logic right then just didn't account for the
row change where I was inputting... Appreciate your help! "Paul C" wrote: are you sure the 3rd formula is OK All three of these formuls seem to be set up to reference the desired ranges from row 45 45-34=11 45-17=28 Good for row 45 and would be SUM(I11-I28)/I28 45-32=13 45-15=30 Good for row 45 and would be SUM(I13-I30)/I3 To put the formula in Row 46 you need to change the math a little 46-33=13 46-16=30, so your formula for would need to read "=SUM(R[-33]C-R[-16]C)/R[-16]C" The same logic would follow for the 3rd formula which is intended to go in row 47 -- If this helps, please remember to click yes. "Louise" wrote: Hi, I have the following code in VBA to give me some variance % results. Although the rows are always static the columns are not: ActiveCell.FormulaR1C1 = "=SUM(R[-34]C-R[-17]C)/R[-17]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-32]C-R[-15]C)/R[-15]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-30]C-R[-13]C)/R[-13]C" What I want this to do is to start in row 45, in the column i am in (worked out prior to this in the macro) and put in a formula which basically in Excel would read: =SUM(I11-I28)/I28 then down one row =SUM(I13-I30)/I30 then down one row =SUM(I15-I32)/I32 This works, but only for the 1st and 3rd formulae, the second one is always wrong by one row... Am I doing something majorly wrong here? Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple subtraction formula returning strange results = Excel glitc | Excel Worksheet Functions | |||
VLOOKUP formula results strange after copying down | Excel Worksheet Functions | |||
Use dynamic column reference without R1C1 | Excel Worksheet Functions | |||
Copy a R1C1 formula to a user defined range results #NAME? error | Excel Programming | |||
Dynamic Range in Macro with R1C1 | Excel Programming |