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?
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.