Excel VBA Replace Formula Array

Hello,

I am trying to make the cell B5 to receive an Array Formula. By Researching on Internet I could see there is a limitation of 255 characters on Array Formulas, this way, it is needed to brake down the formula in two pieces and use Replace function, but I still get the error:

"Unable to set the FormulaArray property of the Range class"

The original formula is: =IFERROR(AVERAGE(IF(('@Daily Data Temp'!$F2:$F3942='S1DAY P1(Ongoing)'!$F$1)*('@Daily Data Temp'!$E2:$E3942='S1DAY P1(Ongoing)'!$G$1)*('@Daily Data Temp'!$D2:$D3942='S1DAY P1(Ongoing)'!$A5)*('@Daily Data Temp'!$J2:$J3942='S1DAY P1(Ongoing)'!$H$1)*('@Daily Data Temp'!$B2:$B3942=$I$1)*('@Daily Data Temp'!$H2:$H3942=B$3),'@Daily Data Temp'!$I2:$I3942)),"N/A")


But by using the Excel VBA it needs to translate(I used macro to record) and here is the code.

Please Login or Register  to view this content.

Can anyone help me please?

Thanks!

Is the formula being used on either of the @Daily Data Temp or S1DAY P1(Ongoing) sheets, or on another sheet?

Since you're using 2007, you can shorten that formula to:

=IFERROR(AVERAGEIFs('@Daily Data Temp'!$I2:$I3942,'@Daily Data Temp'!$F2:$F3942,$F$1,'@Daily Data Temp'!$E2:$E3942,$G$1,'@Daily Data Temp'!$D2:$D3942,$A5,'@Daily Data Temp'!$J2:$J3942,$H$1,'@Daily Data Temp'!$B2:$B3942,$I$1,'@Daily Data Temp'!$H2:$H3942,B$3),"N/A")

which does not need array-entry, so the length shouldn't be an issue.