Attempting to use FormulaR1C1 referencing multiple cells

Question

I am attempting to use the FormulaR1C1 property in VBA but keep running into a runtime error 1004. My code is as follows.

ActiveCell.FormulaR1C1 = "=R[0]C[-1] * (1 + =R" & cellcount & "C1)"

Any ideas?

Answer 1

From the limited information available, the most likely cause of the 1004 error is the use of ActiveCell. I would recommend you try to fully qualify the cell and see if that fixes the error.

For instance:

Worksheets("Sheet1").cells(1,1).FormulaR1C1 = "=R[0]C[-1] * (1 + R" & cellcount & "C1)"

Again guessing from the limited information available, it looks like you will be applying this to several cells. This is best done through a loop which iterates over the cells. This could be done using a for each statement on a range (perhaps selected by the user?) or through a for statement incriminating a value as needed. I would highly recommend you try fully qualifying just a single cell first as a test before setting up a loop.

Another just generally useful testing method would be to store the formula in a string variable before trying to attach it to the cell. This breakup will give you another step for debugging.

example:

Dim formulaTest as string
formulaTest = "=R[0]C[-1] * (1 + R" & cellcount & "C1)"

Worksheets("Sheet1").cells(1,1).FormulaR1C1 = formulaTest

This with a breakpoint on each line should give you a VERY clear idea of where the error is popping up.