0

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

Vivek Patel
  • 17
  • 2
  • Scott Craners answer gives you the solution. Beside this: It is always a little bit tricky do deal with cell-formulas in VBA. To get the idea what exactly to put in your code, go the other way around first: Write the formula in the cell manually, stay in that cell, go to the VBA-Editor, open the *immediateWindow* (Ctrl+G) and enter `? activecell.Formula, activecell.FormulaR1C1` – FunThomas Mar 08 '19 at 14:55

1 Answers1

1

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"
Scott Craner
  • 148,073
  • 10
  • 49
  • 81