Hi,
I am struggling to find a solution for an Array Formula for the past 7 days. Hope anyone can help me fix this issue. I know it has a limitation of 255 characters for which I googled and found a solution of breaking the formula in to 2-3 strings and rejoining it, but it is not working. I am constantly getting a message "Unable to set the FormulaArray propoerty of the range class". If I try to enter a very short formula, for example, '=Average(N2:O2)' it works but the below formula is giving error. Hope someone can help me fix this issue. Thanks.
The formula is correct, if I enter it with .Formula property it get inserted and works as desired.
I am struggling to find a solution for an Array Formula for the past 7 days. Hope anyone can help me fix this issue. I know it has a limitation of 255 characters for which I googled and found a solution of breaking the formula in to 2-3 strings and rejoining it, but it is not working. I am constantly getting a message "Unable to set the FormulaArray propoerty of the range class". If I try to enter a very short formula, for example, '=Average(N2:O2)' it works but the below formula is giving error. Hope someone can help me fix this issue. Thanks.
Code:
Dim theFormulaPart1 As String Dim theFormulaPart2 As String
Dim theFormulaPart3 As String
Dim theFormulaPart4 As String
theFormulaPart1 = "=IFERROR(IF(AND(Table1[ @[ Data Consistency] ],NOT(SIGN(Table1[ @[ Data missing] ])))," & _
"X_X_X())"
theFormulaPart2 = "SUMPRODUCT((IF((OFFSET(" & ColumnLetter & "" & _
" 2,0,0,1,nbCol" & CBS & "))=""?"",0,1))," & _
"Lst_MaxWeight_" & CBS & "," & _
"Y_Y_Y())"
theFormulaPart3 = "SIGN(TRANSPOSE(FREQUENCY(MATCH(Lst_Indic_" & _
"" & CBS & "," & _
"Lst_Indic_" & CBS & ",0)," & _
"Z_Z_Z())"
theFormulaPart4 = "COLUMN(OFFSET($S$8,0,0,1,nbCol" & CBS & "-1))" & _
"-COLUMN($" & ColumnLetter & "$8)+1)))),""""),""ND"")"
With Worksheets("Sheet1").ListObjects("Table1").ListColumns(CBS & " Row Score Max").DataBodyRange.Cells(1)
.FormulaArray = theFormulaPart1
.Replace "X_X_X())", theFormulaPart2
.Replace "Y_Y_Y())", theFormulaPart3
.Replace "Z_Z_Z())", theFormulaPart4
'.FormulaArray = .FormulaR1C1
' .Select
' DoEvents
' Application.SendKeys "{F2}^+~"
End With
Last edited: