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.
Sub Macro7()
Dim Formula1 As String
Dim Formula2 As String
Formula1 = "=IFERROR(AVERAGE(IF(('@Daily Data Temp'!R[-3]C6:R[3937]C6='S1DAY P1(Ongoing)'!R1C6)*('@Daily Data Temp'!R[-3]C5:R[3937]C5='S1DAY P1(Ongoing)'!R1C7)*('@Daily Data Temp'!R[-3]C4:R[3937]C4='S1DAY P1(Ongoing)'!RC1)*" & _
"X_X"
Formula2 = "('@Daily Data Temp'!R[-3]C10:R[3937]C10='S1DAY P1(Ongoing)'!R1C8)*('@Daily Data Temp'!R[-3]C2:R[3937]C2=R1C9)*('@Daily Data Temp'!R[-3]C8:R[3937]C8=R3C),'@Da" & _
"Temp'!R[-3]C9:R[3937]C9)),""N/A"")"
With ActiveSheet.Range("B5")
.FormulaArray = Formula1
.Replace "X_X", Formula2
End With
End Sub
Can anyone help me please?
Thanks!
Bookmarks