Getting error 1004 trying to write formula in cell

[Guest]

In a macro, I wanted to be able to update a formula. I manually updated the
formula on the sheet and it works fine. I simply copied the formula from the
sheet and put it in quotes see below, but I get an error

"Application-defined or object-defined error"


Sheets("test").Range("A1").Formula = "=IF(ISERROR(VLOOKUP($AE6,'Change
Report 6.0'!$C$20:$G$61,AG$4,FALSE)),"",VLOOKUP($AE6,'Change Report
6.0'!$C$20:$G$61,AG$4,FALSE))"

The Help on the error seems to suggest I tried to cause an error, clearly
this is not my intention. Any ideas?

 

[Leith Ross]

Hello Trefor,

Is the Formula all on one line? if you break it you must use the line
continuation character (Underscore) preceded by a space at the end of
the line. If you don't, it won't compile.

Example:
If your code really looks like this, it won't run...

Sheets("test").Range("A1").Formula = "=IF(ISERROR(VLOOKUP($AE6,'Change
Report 6.0'!$C$20:$G$61,AG$4,FALSE)),"",VLOOKUP($AE6,'Cha nge Report
6.0'!$C$20:$G$61,AG$4,FALSE))"

If it has the line continuation characters it will...

Sheets("test").Range("A1").Formula = "=IF(ISERROR(VLOOKUP($AE6,'Change
_
Report 6.0'!$C$20:$G$61,AG$4,FALSE)),"",VLOOKUP($AE6,'Cha nge Report _
6.0'!$C$20:$G$61,AG$4,FALSE))"

Sincerely,
Leith Ross

 

[Guest]

Change the empty quotations in the formula ("") to empty double quotations
(""""). This is a requirement when quotation marks are themselves contained
within quotation marks.

Regards,
Greg

 

[Guest]

Greg,

Spot on, many thanks.

--
Trefor

Change the empty quotations in the formula ("") to empty double quotations
(""""). This is a requirement when quotation marks are themselves contained
within quotation marks.

Regards,
Greg

 

[Guest]

Leith,

Many thanks for your fedback. It was all on one line, just the way this
systems works. Greg nailed it, it was double quotes.

 

[Guest]

Greg,

Do you know why the following code errors?

Sheets("test").Range("J1").Formula = "=IF(C9="""","TBA",C9)"

 

[Dave Peterson]

You missed a couple of quotes when you doubled them up:

Sheets("test").Range("J1").Formula = "=IF(C9="""",""TBA"",C9)"

(surrounding TBA)