0

I Have a macro containing a line that will change the formula of a cell using R1C1 formula type.

The formula is:

    ActiveCell.FormulaR1C1 = _
    "=IF(R[0]C[-2]=0,"",(R[0]C[-20]-R[0]C[-16]))"

When ever I attempt to run the macro it always comes up with a dialog box saying

    Run-time error '1004':
    Application-defined or object-defined error.

And when you click debug it highlights those 2 lines in the macro. And I can't figure out how to fix it. Can anyone help?

2

your are missing a close bracket

ActiveCell.FormulaR1C1 = _
    "=IF(R[0]C[-2]=0,"""",(R[0]C[-20]-R[0]C[-16]))"

Edit: corrected to put "" in to formula

0

Your problem seems to be the quotes, have you tried changing or escaping the quotation marks?

0

Try use it:

   ActiveCell.FormulaR1C1 = _
    "=IF(R[0]C[-2]=0," & Chr(34) & Chr(34) & ",(R[0]C[-20]-R[0]C[-16]))"

Remember this formula cannot be placed before column 20 or an error will be raised.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.