I have an excel macro that creates a new excel sheet and fills it with a variable number of rows. I have a count of the number of rows stored in a variable. I'm using an R1C1 formula to multiply two cells together based on my row count. In the macro the formula looks like it's correct (if I add a break point and put a watch on it) but when the macro finishes the formula in the cell is incorrect.
VB Code to generate formula:
MainSheet.Cells(RowCount + 6, 6).FormulaR1C1 = "=R[" & RowCount + 3 & "]C" & "*" & "R[" & RowCount + 4 & "]C"
Watch on the formula with a break point reveals this: "=R[430]C*R[431]C" Actual cell data after macro finishes: =F863*F864. I need this to be =F430*F431
By using the [] you are telling vba that you want the relative positioning. Which means it will add(or subtract) the number in the [] to the current row and/or column in which the formula is being placed.
Remove the RowCount:
MainSheet.Cells(RowCount + 6, 6).FormulaR1C1 = "=R[3]C*R[4]C"