Getting error 1004 trying to write formula in cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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
 
Greg,

Spot on, many thanks.

--
Trefor


Greg Wilson said:
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
 
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.
 
Greg,

Do you know why the following code errors?

Sheets("test").Range("J1").Formula = "=IF(C9="""","TBA",C9)"
 
You missed a couple of quotes when you doubled them up:

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

(surrounding TBA)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top