1

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?

Sam Mcdonald
  • 25
  • 3
  • Maybe you are trying to get `ActiveCell.FormulaR1C1 = "=R[0]C[-1] * (1 + R" & cellcount & "C1)"` . It would be easier if you let us know what your formula should do – Shai Rado Jan 30 '17 at 17:48
  • The formula is obtaining values in cells that will be in variable locations (i.e. cellcount) and multiplying the value in the cell to the left, by a growth rate that is retrieved by the R" & cellcount &" C1 bit of code. – Sam Mcdonald Jan 30 '17 at 18:00
  • can you post an example in a worksheet how your formula should be applied ? In which cell you want the formula, and to what cells it is looking ? – Shai Rado Jan 30 '17 at 18:05

1 Answers1

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.

Mikey Awbrey
  • 94
  • 2
  • 8
  • 1
    Thank you Mike, I was using a For loop to loop through a variable number of cells, I believe it was a small syntax error. Rearranging the code as so worked: "=R[0]C[-1] * ( R" & cellcount & "C1 + 1)" – Sam Mcdonald Jan 30 '17 at 21:21
  • That is strange... I tested `"=R[0]C[-1] * (1 + R" & cellcount & "C1)"` and the only error I could sort of see in the syntax is that `R[0]C[-1]` is automatically shortened to `RC[-1]`. I do not see how having the `+1` before or after the `"R" & cellcount & "C1"` segment makes the difference. If I remember, I'll do some more testing for very large numbers of rows and see if that is where it gets weird. – Mikey Awbrey Jan 31 '17 at 21:58