VBA Formula error

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Array Help in VBA 3
Apply ADDRESS formula in VB 6
Indirect and matching formula isn't working 3
Finding dynamic maxima 8
Evaluate an Array Formula before inserting it in a cell 9
Evalute Array Formula in VBA code 2
End of Statement error 3
VBA / Formula 2

Top