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?

 

[johnnyL]

Same here.

 

[Fluff]

Probably the best option would be to start a new thread & see if anyone can convert that formula into a non-array version.

 

[johnnyL]

I may end up doing that. Thank you for the advice @Fluff. I am still going to read till my eyes bleed to see if I can figure this out.

 

[johnnyL]

I think I might have something for this, I just need to wait for the spirits to subside so I can finish it up.

 

[johnnyL]

Ok, started a new thread

I think I might have something for this, I just need to wait for the spirits to subside so I can finish it up.

Work around script for when VBA array formula gives error 1004 Unable to set the FormulaArray property of the Range class

Sub VBA_ArrayFormulaChopperV1() ' ' Inspired by some code originally posted by B_Nut. ' https://stackoverflow.com/questions/22949221/array-formula-with-more-than-255-characters ' ' This current code has been drastically changed. It is designed to create the code needed to 'work around' a...

www.mrexcel.com