Runtime Error 1004 Copy long array formula in vba

Question

I have the following array formula in cell B2 in my Excel spreadsheet:

={IF(COUNT(IF(ISNUMBER(A30:A1000);IF(C30:C1000>A30:A1000-1;A30:A1000)))>=COUNT(IF(ISNUMBER(A30:A1000);IF(B30:B1000>A30:A1000-1;A30:A1000)));COUNT(IF(ISNUMBER(A30:A1000);IF(C30:C1000>A30:A1000-1;A30:A1000)));IF(COUNT(IF(ISNUMBER(A30:A1000);IF(C30:C1000>A30:A1000-1;A30:A1000)))<=COUNT(IF(ISNUMBER(A30:A1000);IF(B30:B1000>A30:A1000-1;A30:A1000)));COUNT(IF(ISNUMBER(A30:A1000);IF(B30:B1000>A30:A1000-1;A30:A1000)));COUNT(IF(ISNUMBER(A30:A1000);IF(C30:C1000>A30:A1000-1;A30:A1000)))))}

Now I want to use the following VBA code to copy this code into cell A2:

Sheets("Sheet1").Range("A2").FormulaArray = Sheets("Sheet1").Range("B2").Formula

However, when I use this code I get runtime error 1004.

Do you have any idea how to solve this issue?

Answer 1

I believe the issue lies with the references within the formula. As the cell B2 references A30:A1000 in the formula, when you apply this to the cell A2 it tries to move the reference one column to the left - which it can't. In order to bypass this you can make the references absolute:

={IF(COUNT(IF(ISNUMBER($A$30:$A$1000);IF($C$30:$C$1000>$A$30:$A$1000-1;$A$30:$A$1000)))>=COUNT(IF(ISNUMBER($A$30:$A$1000);IF($B$30:$B$1000>$A$30:$A$1000-1;$A$30:$A$1000)));COUNT(IF(ISNUMBER($A$30:$A$1000);IF($C$30:$C$1000>$A$30:$A$1000-1;$A$30:$A$1000)));IF(COUNT(IF(ISNUMBER($A$30:$A$1000);IF($C$30:$C$1000>$A$30:$A$1000-1;$A$30:$A$1000)))<=COUNT(IF(ISNUMBER($A$30:$A1000);IF(B30:B1000>A30:A$1000-1;$A$30:$A$1000)));COUNT(IF(ISNUMBER($A$30:$A$1000);IF($B$30:$B$1000>$A$30:$A$1000-1;$A$30:$A$1000)));COUNT(IF(ISNUMBER($A$30:$A$1000);IF($C$30:$C$1000>$A$30:$A$1000-1;$A$30:$A$1000)))))}

Answer 2

character limit of 255 when passing arrays...

https://support.microsoft.com/en-us/kb/213181

Set up named ranges for a30:A1000 of A, B30:B1000 of B, and c30:c1000 of D (C is reserved so you can't use it and CC will make this formula too long also) (ctrl+f3 to open the named range manager and then hit new button)

Then Change your formula to use the named ranges.

=IF(COUNT(IF(ISNUMBER(A),IF(D>A-1,A)))>=COUNT(IF(ISNUMBER(A),IF(B>A-1,A))),COUNT(IF(ISNUMBER(A),IF(D>A-1,A))),IF(COUNT(IF(ISNUMBER(A),IF(D>A-1,A)))<=COUNT(IF(ISNUMBER(A),IF(B>A-1,A))),COUNT(IF(ISNUMBER(A),IF(B>A-1,A))),COUNT(IF(ISNUMBER(A),IF(D>A-1,A)))))

Alternately you could set up custom VBA functions that would allow you to replace the repetitive code within your if statement with shorter strings of characters to bring you under the 255. But ultimately you need to get under 255. Once under 255 your code works fine, either with my changes above or with a shorter array formula. (note that I changed the semi-colons to commas so you might need to change them back if you use semi-colons)