VBA to use evaluate with index match returns #VALUE! error

Hi

I am currently attempting to evaluate the following in VBA but get the #VALUE! error.

Please Login or Register  to view this content.

Is someone able to help me to see where I'm going wrong?

Thanks

Hi,

Need one more closing bracket :

..........*(Summary!$N1:$N300000 = 0),0)")
..........*(Summary!$N1:$N300000 = 0),0))")

Perfect! Thank you

Any ideas how I can turn this into a variable?

What I'm trying to achieve is to have this in each cell in col AI. The above array formula when entered directly is much too slow when copied down 300000 rows!

Thanks

You are welcome, thanks for the rep. points.

Turn into a variable: do you mean turn into a function (as a replacement to Excel formula) ? It is called UDF (User Defined Function).
But based on your very huge data (300.000 rows x 16 columns), it is unwised to do so, since an Excel Formula, and also an UDF, is auto recalc.
Everytime you change something in affacted range, the 300.000 Formulas/UDFs will recalculate, this will make Excel not responsive.
I suggest you use a normal sub instead, and call this sub manually when needed.

You can upload a small subset of your workbook, so the layout can be analyzed, and some Experts here can help you to make the UDF.