phrankndonna
12-21-2011, 12:25 PM
Hi,
I've got a worksheet in Excel 2003 where a variable number of rows of data get added. Following the data there is a blank row, then a formula is entered in the following row (e.g., data in rows 2-10, row 11 is blank, and row 12 has the formulas referring back to rows 2-10). The formula is =((D2/$B2)-1)*100. The formula then gets copied down the analagous number of rows for which there are data. I.E., if there are 10 rows of data, then this code would enter the formula on row 13 and get copied down to row 22, and it would be referring to the data in rows 2 through 11.
I recorded the macro with 5 rows of data, and it works - as long as there are exactly 5 rows of data. How do I get the R[-6] to change along with the number of rows? Thanks
Range("D2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=((R[-6]C/R[-6]C2)-1)*100"
Frank
I've got a worksheet in Excel 2003 where a variable number of rows of data get added. Following the data there is a blank row, then a formula is entered in the following row (e.g., data in rows 2-10, row 11 is blank, and row 12 has the formulas referring back to rows 2-10). The formula is =((D2/$B2)-1)*100. The formula then gets copied down the analagous number of rows for which there are data. I.E., if there are 10 rows of data, then this code would enter the formula on row 13 and get copied down to row 22, and it would be referring to the data in rows 2 through 11.
I recorded the macro with 5 rows of data, and it works - as long as there are exactly 5 rows of data. How do I get the R[-6] to change along with the number of rows? Thanks
Range("D2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=((R[-6]C/R[-6]C2)-1)*100"
Frank