Excel Help: Macro is not Cooperating with Quotations!

Question

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?

Answer 1

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

Answer 2

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

Answer 3

I believe you want to use

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

i.e. use the single quote to state what should go into the cell, this means that everything inside the single quotes should be taken as the value to go into the cell including the actual quotation marks.

Answer 4

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.