Getting error 1004 trying to write formula in cell
 

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,'Cha nge 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?

--
Trefor

Getting error 1004 trying to write formula in cell
 

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=487497

Getting error 1004 trying to write formula in cell
 

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

"Trefor" wrote:

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,'Cha nge 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?

--
Trefor

Getting error 1004 trying to write formula in cell
 

Greg,

Spot on, many thanks.

--
Trefor


"Greg Wilson" wrote:

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

"Trefor" wrote:

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,'Cha nge 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?

--
Trefor

Getting error 1004 trying to write formula in cell
 

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.

--
Trefor


"Leith Ross" wrote:


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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=487497

Getting error 1004 trying to write formula in cell
 

Greg,

Do you know why the following code errors?

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

--
Trefor


"Trefor" wrote:

Greg,

Spot on, many thanks.

--
Trefor


"Greg Wilson" wrote:

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

"Trefor" wrote:

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,'Cha nge 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?

--
Trefor

Getting error 1004 trying to write formula in cell
 

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

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

(surrounding TBA)


Trefor wrote:

Greg,

Do you know why the following code errors?

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

--
Trefor

"Trefor" wrote:

Greg,

Spot on, many thanks.

--
Trefor


"Greg Wilson" wrote:

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

"Trefor" wrote:

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,'Cha nge 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?

--
Trefor

--

Dave Peterson