FormulaArray Error - Unable to set the FormulaArray property of the range class

[eoinymc]

Hi guys,

Wondering if anyone can help me.

I am getting an error: "Unable to set the FormulaArray property of the range class" when I try to run the following code:

Range("AK3").FormulaArray = "=IF(ISNUMBER(SEARCH(1,$Y3)),SUM((MONTH($Z3-1+(ROW(INDIRECT(""1:""&$AA3-$Z3+1))))=AK$2)*(WEEKDAY($Z3-1+(ROW(INDIRECT(""1:""&$AA3-$Z3+1))),11)=1)),0) + IF(ISNUMBER(SEARCH(2,$Y3)),SUM((MONTH($Z3-1+(ROW(INDIRECT(""1:""&$AA3-$Z3+1))))=AK$2)*(WEEKDAY($Z3-1+(ROW(INDIRECT(""1:""&$AA3-$Z3+1))),11)=2)),0)"

When I take out half of the formula, it seems to work though..ie, the following works:

Range("AK3").FormulaArray = "=IF(ISNUMBER(SEARCH(1,$Y3)),SUM((MONTH($Z3-1+(ROW(INDIRECT(""1:""&$AA3-$Z3+1))))=AK$2)*(WEEKDAY($Z3-1+(ROW(INDIRECT(""1:""&$AA3-$Z3+1))),11)=1)),0)"

When I paste in the full range into excel and do the ctrl+Shift+enter, the long one works!

Has anyone got any ideas as to why this isn't working?

Thanks,

Eoin

 

[eoinymc]

I found the answer here:
Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA