VBA Formula error

[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

 

[Leith Ross]

Hi John,

How are you using this formula in VBA?

Thanks,
Leith Ross

 

[Rowan Drummond]

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

 

[John McClain]

Rowan,

That worked like a champ! Thanks very much.

John

 

[Rowan Drummond]

You're welcome.