VBA array formula gives error 1004 Unable to set the FormulaArray property of the Range class

[johnnyL]

wsDestination.Range("J2").FormulaArray = "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(G2-$G$2:$G$20000)<=1)*(C2=$C$2:$C$20000)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""""),MIN(SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000=""PORTAL""),1,0)),SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000=""TALLY""),1,0)))),""Matched"",NA()),NA())"

results in error mentioned.

wsDestination.Range("J2").Formula = "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(G2-$G$2:$G$20000)<=1)*(C2=$C$2:$C$20000)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""""),MIN(SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000=""PORTAL""),1,0)),SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000=""TALLY""),1,0)))),""Matched"",NA()),NA())"

The above writes a non array formula successfully.

Why does the first example result in error?

 

[Fluff]

The formula is too long, there is a limit of (I think) 255 characters for FormulaArray.

 

[johnnyL]

Correct. I am having difficulty in the proper way to use string variables to shorten it. I have it shortened to about 240 characters with the added variables, but I still get the error.

 

[Fluff]

Try it like

Dim b As String
b = "MIN(SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000=""PORTAL""),1,0)),SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000=""TALLY""),1,0)))"

With Range("J2")
   .FormulaArray = "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(G2-$G$2:$G$20000)<=1)*(C2=$C$2:$C$20000)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""""),xxxxxx),""Matched"",NA()),NA())"
   .Replace "xxxxxx", b, xlPart
End With

 

[johnnyL]

Dang it. I was approaching it the same way, but I didn't have the ', xlPart'

Thank you so much, once again @Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[johnnyL]

One more question please. Filling down that array formula is taking a long time. I have also tried copy/paste.

The ways that seem to give fast results don't increment the addresses. It is the same formula all the way down.

Any suggestions?

 

[Fluff]

You could try converting it to an index/aggregate formula if it doesn't need to run on xl 2007, failing that use helper columns.

 

[johnnyL]

Try it like

Dim b As String
b = "MIN(SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000=""PORTAL""),1,0)),SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000=""TALLY""),1,0)))"

With Range("J2")
   .FormulaArray = "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(G2-$G$2:$G$20000)<=1)*(C2=$C$2:$C$20000)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""""),xxxxxx),""Matched"",NA()),NA())"
   .Replace "xxxxxx", b, xlPart
End With

Could those be chopped in half, or so, again? I need to use some variables and they eat up like 10 characters each by the time you figure in the quotes and spaces and '&'s.

 

[Fluff]

Possibly, but it's not something I know much about as I've never needed to do it.