#Value error on entering a formula using VBA

Hi all,

I've been struggling with this particular query for a couple of hours now, have googled plently, recorded a macro, inserted "'s and &'s all over the place but not been able to find a solution.

I have a table within a spreadsheet which uses various sumproduct / indirect / match formulas to collate data from underlying spreadsheets. The formulas are entered by clicking on an update button, calculating then overpasting with the values (keeps it small in size and runs nice and quick).

All but one of the formulas are working fine - with double quotations all over the place - but this particular one is returning a #value error which I can fix by removing the quotation marks in the formula bar. I cannot however get the right combination in VBA code to enter the formula in a way that works, please help!

Code:

Please Login or Register  to view this content.

Formula bar (cell displays #value):

Please Login or Register  to view this content.

This is the formula I'm trying to reach:

Please Login or Register  to view this content.

Any help appreciated...

Thanks Mordred - didn't quite work, returned FALSE, but with a tweak it's perfect!

Please Login or Register  to view this content.

I was under the imrpession (and I'm damn sure it did when I tried before) that apostophes throw out errors as VBA thinks of them as annotation. But this works and I'm taking it - thanks again.