VBA Debug - Run-time 1004: R1C1 Formula Not Calculating

Question

I'm trying to write some VBA scripting that embodies an Excel formula, but keep getting an error and can't seem to find a solution anywhere.

I think that what might be causing the problem is that I have an R1C1 formula that includes a +1. I can't tell for sure, but it seems possible that it could be construed as bad syntax. Perhaps making it think I'm referring to the current column + 1 column. Total stretch though.

Range("A4").Select
ActiveCell.FormulaR1C1 = _
    "=IF(R[-1]C+1>R2C15,"",R[-1]C+1)"

The end result should be that if the previous row's value +1 is greater than the value in R2C15 (O2), it will produce a blank, otherwise the previous row's value +1. However, I keep getting the 1004 error.

Any thoughts?

turn this: `"=IF(R[-1]C+1>R2C15,"",R[-1]C+1)"` into this: `"=IF(R[-1]C>VALUE(RC)+1,"",VALUE(R[-1]C)+1)"` — Damian, May 30 '19 at 06:12
This as well as Gareth's answer were what resolved this error. Thank you! — Mesanic, May 31 '19 at 03:16

score 1 · Accepted Answer · answered May 30 '19 at 07:34

1

You haven't escaped your quotation marks.

If you are new to VBA it takes some getting used to but you need to use

Range("A4").Select
ActiveCell.FormulaR1C1 = _
    "=IF(R[-1]C+1>R2C15,"""",R[-1]C+1)"

answered May 30 '19 at 07:34

This as well as Damian's answer were what resolved this error. Thank you! – Mesanic May 31 '19 at 03:17

VBA Debug - Run-time 1004: R1C1 Formula Not Calculating

1 Answers1