VBA Formula error

  • Thread starter Thread starter John McClain
  • Start date Start date
J

John McClain

Hi there,

In the following formula, VBA interprets the single quotes as a comment line
when I use the ActiveCell.Formula function:

=IF(ISERROR(SUM(INDEX(INDIRECT("'"&WORK_ORDER&"'!1:10000"),MATCH($J4,INDIRECT("'"&WORK_ORDER&"'!$A$1:$A$10000"),0)+1,MATCH(T$1,INDIRECT("'"&WORK_ORDER&"'!2:2"),0)):INDEX(INDIRECT("'"&WORK_ORDER&"'!1:10000"),MATCH($J4,INDIRECT("'"&WORK_ORDER&"'!$A$1:$A$10000"),0)+1,MATCH(T$2,INDIRECT("'"&WORK_ORDER&"'!2:2"),0)))=TRUE),0,SUM(INDEX(INDIRECT("'"&WORK_ORDER&"'!1:10000"),MATCH($J4,INDIRECT("'"&WORK_ORDER&"'!$A$1:$A$10000"),0)+1,MATCH(T$1,INDIRECT("'"&WORK_ORDER&"'!2:2"),0)):INDEX(INDIRECT("'"&WORK_ORDER&"'!1:10000"),MATCH($J4,INDIRECT("'"&WORK_ORDER&"'!$A$1:$A$10000"),0)+1,MATCH(T$2,INDIRECT("'"&WORK_ORDER&"'!2:2"),0))))

The formula is valid when I tyoe it manually in Excel.

I have also tried changing single quotes to doubles, doubles to "double
double" etc, and no luck. Any suggestions would be greatly appreciated.

Thanks,

John
 
Leave the single quotes as is and double all the internal double quotes eg:

Range("C5").Formula = "=IF(ISERROR(SUM(INDEX(INDIRECT" _
& "(""'""&WORK_ORDER&""'!1:10000""),MATCH($J4,INDIRECT" _
& "(""'""&WORK_ORDER&""'!$A$1:$A$10000""),0)+1,MATCH" _
& "(T$1,INDIRECT(""'""&WORK_ORDER&""'!2:2""),0)):INDEX" _
& "(INDIRECT(""'""&WORK_ORDER&""'!1:10000""),MATCH" _
& "($J4,INDIRECT(""'""&WORK_ORDER&""'!$A$1:$A$10000"")" _
& ",0)+1,MATCH(T$2,INDIRECT(""'""&WORK_ORDER&""'!2:2"")" _
& ",0)))=TRUE),0,SUM(INDEX(INDIRECT" _
& "(""'""&WORK_ORDER&""'!1:10000""),MATCH($J4,INDIRECT" _
& "(""'""&WORK_ORDER&""'!$A$1:$A$10000""),0)+1,MATCH" _
& "(T$1,INDIRECT(""'""&WORK_ORDER&""'!2:2""),0)):INDEX" _
& "(INDIRECT(""'""&WORK_ORDER&""'!1:10000""),MATCH" _
& "($J4,INDIRECT(""'""&WORK_ORDER&""'!$A$1:$A$10000"")" _
& ",0)+1,MATCH(T$2,INDIRECT(""'""&WORK_ORDER&""'!2:2""),0))))"

Hope this helps
Rowan
 
Back
Top