I have a macro that writes formulas into several cells. The first 2 work
great but the 3rd and the 4th ones give me an End of Statement error. On the
3rd one it all turns red and gives the error at the "2" at the first
....7,FALSE)= "2"), and likewise at the "1" in the 4th formula.

Here are the 4 formulas. Incidentally these formulas work fine on the
spreadsheet.

ActiveCell.Formula = _

"IF(C24=0,"",IF(ISNA(VLOOKUP(VLOOKUP(($N$8&$C2 4),([Atlanta.xls]item!$B$3:$G$10000),4,FALSE),([Atlanta.xls]Vendor!$A$2:$L$10000),12,FALSE)),"",VLOOKUP(VLOOKU P(($N$8&$C24),([Atlanta.xls]item!$B$3:$G$7002),4,FALSE),([Atlanta.xls]Vendor!$A$2:$L$10000),12,FALSE)))"
ActiveCell.Offset(0, 2).Select

ActiveCell.Formula = _

"IF(ISNA(VLOOKUP(MID(C19,FIND(LEFT(SUBSTITUTE(C19, 0,"")),C19),255),([Atlanta.xls]Atl!$B$1:$L$10000),9,FALSE)),0,IF(C19=0,0,VLOOKUP( MID(C19,FIND(LEFT(SUBSTITUTE(C19,0,"")),C19),255), ([Atlanta.xls]Atl!$B$1:$L$10000),9,FALSE)))"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = _
"=IF(ISNA(VLOOKUP($C21,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)=
"2"),F21/(1-M21),IF(F21=0,0,IF((VLOOKUP($C21,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)="2"),VLOOKUP(MID(C21,F IND(LEFT(SUBSTITUTE(C21,0,"")),C21),255),([Atlanta.xls]Atl!$B$1:$L$10000),11,FALSE),F21/(1-M21))))"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = _

"IF(ISNA(VLOOKUP($C24,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)),"1",VLOOKUP($C24,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE))"

Any Ideas?

Thanks,

Squeaky

I realized I left out a few of the = signs. When I put them in the code, the
only one that actually works is the first formula. The rest give me an
application defined or object defind error.

"Squeaky" wrote:

I have a macro that writes formulas into several cells. The first 2 work
great but the 3rd and the 4th ones give me an End of Statement error. On the
3rd one it all turns red and gives the error at the "2" at the first
...7,FALSE)= "2"), and likewise at the "1" in the 4th formula.

Here are the 4 formulas. Incidentally these formulas work fine on the
spreadsheet.

ActiveCell.Formula = _

"IF(C24=0,"",IF(ISNA(VLOOKUP(VLOOKUP(($N$8&$C2 4),([Atlanta.xls]item!$B$3:$G$10000),4,FALSE),([Atlanta.xls]Vendor!$A$2:$L$10000),12,FALSE)),"",VLOOKUP(VLOOKU P(($N$8&$C24),([Atlanta.xls]item!$B$3:$G$7002),4,FALSE),([Atlanta.xls]Vendor!$A$2:$L$10000),12,FALSE)))"
ActiveCell.Offset(0, 2).Select

ActiveCell.Formula = _

"IF(ISNA(VLOOKUP(MID(C19,FIND(LEFT(SUBSTITUTE(C19, 0,"")),C19),255),([Atlanta.xls]Atl!$B$1:$L$10000),9,FALSE)),0,IF(C19=0,0,VLOOKUP( MID(C19,FIND(LEFT(SUBSTITUTE(C19,0,"")),C19),255), ([Atlanta.xls]Atl!$B$1:$L$10000),9,FALSE)))"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = _
"=IF(ISNA(VLOOKUP($C21,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)=
"2"),F21/(1-M21),IF(F21=0,0,IF((VLOOKUP($C21,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)="2"),VLOOKUP(MID(C21,F IND(LEFT(SUBSTITUTE(C21,0,"")),C21),255),([Atlanta.xls]Atl!$B$1:$L$10000),11,FALSE),F21/(1-M21))))"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = _

"IF(ISNA(VLOOKUP($C24,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)),"1",VLOOKUP($C24,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE))"

Any Ideas?

Thanks,

Squeaky

Now that I made a real mess of this posting, I actually left out the first
code that works. Here it is.

Range("B24").Select
ActiveCell.Formula = _

"=IF(ISNA(VLOOKUP(MID(C24,FIND(LEFT(SUBSTITUTE(C24 ,0,"""")),C24),255),([Atlanta.xls]item!$B$1:$G$10000),5,FALSE)),"""",IF(C24=0,"""",( VLOOKUP(MID(C25,FIND(LEFT(SUBSTITUTE(C24,0,"""")), C24),255),([Atlanta.xls]item!$B$1:$G$10000),5,FALSE))))"

The rest I made by copy/paste of the above and then copy/paste of the
formulas in the quotes.

"Squeaky" wrote:

I have a macro that writes formulas into several cells. The first 2 work
great but the 3rd and the 4th ones give me an End of Statement error. On the
3rd one it all turns red and gives the error at the "2" at the first
...7,FALSE)= "2"), and likewise at the "1" in the 4th formula.

Here are the 4 formulas. Incidentally these formulas work fine on the
spreadsheet.

ActiveCell.Formula = _

"=IF(C24=0,"",IF(ISNA(VLOOKUP(VLOOKUP(($N$8&$C24), ([Atlanta.xls]item!$B$3:$G$10000),4,FALSE),([Atlanta.xls]Vendor!$A$2:$L$10000),12,FALSE)),"",VLOOKUP(VLOOKU P(($N$8&$C24),([Atlanta.xls]item!$B$3:$G$7002),4,FALSE),([Atlanta.xls]Vendor!$A$2:$L$10000),12,FALSE)))"
ActiveCell.Offset(0, 2).Select

ActiveCell.Formula = _

"=IF(ISNA(VLOOKUP(MID(C19,FIND(LEFT(SUBSTITUTE(C19 ,0,"")),C19),255),([Atlanta.xls]Atl!$B$1:$L$10000),9,FALSE)),0,IF(C19=0,0,VLOOKUP( MID(C19,FIND(LEFT(SUBSTITUTE(C19,0,"")),C19),255), ([Atlanta.xls]Atl!$B$1:$L$10000),9,FALSE)))"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = _
"=IF(ISNA(VLOOKUP($C21,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)=
"2"),F21/(1-M21),IF(F21=0,0,IF((VLOOKUP($C21,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)="2"),VLOOKUP(MID(C21,F IND(LEFT(SUBSTITUTE(C21,0,"")),C21),255),([Atlanta.xls]Atl!$B$1:$L$10000),11,FALSE),F21/(1-M21))))"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = _

"=IF(ISNA(VLOOKUP($C24,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)),"1",VLOOKUP($C24,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE))"

Any Ideas?

Thanks,

Squeaky

Nevermind. I figured it out myself.

"Squeaky" wrote:

I have a macro that writes formulas into several cells. The first 2 work
great but the 3rd and the 4th ones give me an End of Statement error. On the
3rd one it all turns red and gives the error at the "2" at the first
...7,FALSE)= "2"), and likewise at the "1" in the 4th formula.

Here are the 4 formulas. Incidentally these formulas work fine on the
spreadsheet.

ActiveCell.Formula = _

"IF(C24=0,"",IF(ISNA(VLOOKUP(VLOOKUP(($N$8&$C2 4),([Atlanta.xls]item!$B$3:$G$10000),4,FALSE),([Atlanta.xls]Vendor!$A$2:$L$10000),12,FALSE)),"",VLOOKUP(VLOOKU P(($N$8&$C24),([Atlanta.xls]item!$B$3:$G$7002),4,FALSE),([Atlanta.xls]Vendor!$A$2:$L$10000),12,FALSE)))"
ActiveCell.Offset(0, 2).Select

ActiveCell.Formula = _

"IF(ISNA(VLOOKUP(MID(C19,FIND(LEFT(SUBSTITUTE(C19, 0,"")),C19),255),([Atlanta.xls]Atl!$B$1:$L$10000),9,FALSE)),0,IF(C19=0,0,VLOOKUP( MID(C19,FIND(LEFT(SUBSTITUTE(C19,0,"")),C19),255), ([Atlanta.xls]Atl!$B$1:$L$10000),9,FALSE)))"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = _
"=IF(ISNA(VLOOKUP($C21,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)=
"2"),F21/(1-M21),IF(F21=0,0,IF((VLOOKUP($C21,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)="2"),VLOOKUP(MID(C21,F IND(LEFT(SUBSTITUTE(C21,0,"")),C21),255),([Atlanta.xls]Atl!$B$1:$L$10000),11,FALSE),F21/(1-M21))))"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = _

"IF(ISNA(VLOOKUP($C24,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE)),"1",VLOOKUP($C24,([Atlanta.xls]Atl!$B$1:$L$10000),7,FALSE))"

Any Ideas?

Thanks,

Squeaky