Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 2 down vote accepted

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

share|improve this answer
    
Great that Worked! Thanks. –  B-Ballerl Feb 27 '11 at 8:31

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

share|improve this answer

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.

share|improve this answer
    
This also displays an error. Compile Error: Expected: Expression and it highlights the first single quotation –  B-Ballerl Feb 27 '11 at 1:40

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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