| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
| How to write a formula that will prevent appearance ERROR MESSAGE | Excel Worksheet Functions | |||
| Protect Sheet but enable cell ranges giving Error 1004 Unable to set | Excel Programming | |||
| Error when using macro to write formula to cell | Excel Programming | |||
| Excel Formula - Error 1004 - Bug? Mistake? | Excel Programming | |||
Hybrid Mode
