Solved: Worksheet formula in VBA

[phrankndonna]

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

[p45cal]

Set LR = Range("D2").End(xlDown)
LR.Offset(2, 0).Resize(LR.Row - 1).Formula = "=((D2/$B2)-1)*100"
'LR.Offset(2, 0).Resize(LR.Row - 1, 5).Formula = "=((D2/$B2)-1)*100"

The commented out line is an alternative if you want to copy the formula across a number of cells as well, as the absolute/relative refs in the formula imply that you may want to do this.

[phrankndonna]

Wow! That worked perfectly! The commented out line also worked perfectly; however, I need to copy the formula over every other column because the table is split into Team A and Team B. The same formula but with a different refernce is going into column E (i.e., columns D, F, H, J, and L refer to and calculate off of column B, while columns E, G, I, K, and M refer to and calculate off of column C).

This gets me much closer to where I need to be. Thank you VERY much!

Frank