Runtime Error 1004 Copy Formula Array

Question

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

{=IF(COUNT(IF(ISNUMBER(A30:A1000);IF(B30:B1000>A30:A1000-1;A30:A1000)))>=COUNT(IF(ISNUMBER(A30:A1000);COUNT(B30:B1000>A30:A1000-1;A30:A1000)));COUNT(IF(ISNUMBER(A30:A1000);COUNT(B30:B1000>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?

That is true. I typed it wrong in my post here. In my Excel file it is correct but the issue with the runtime error does still happen. — Michi
It is a typing error. Sorry. It should be B instead of AP. However, even if I correct it the runtime error still happens. — Michi

Unknown Unknown · Accepted Answer · 2016-06-24T08:20:56

Your array formula is too long to pass along like that as the Range.FormulaArray property.

You don't need to keep repeating all of the conditions. As you cycle through rows 30 to 1000, if the first or second condition fails, the remainder of the formula for that cycle is not processed. IFs in a formula stop processing at the first FALSE.

=IF(COUNT(IF(ISNUMBER(A30:A1000), IF(B30:B1000>A30:A1000-1, A30:A1000)))>=COUNT(B30:B1000>A30:A1000-1,A30:A1000),COUNT(B30:B1000>A30:A1000-1,A30:A1000))

Now the code works just fine.

With Worksheets("Sheet3")
    .Range("A2").FormulaArray = .Range("b2").Formula
End With

Note that I could not test this using semi-colons as the system list separator; only with my own system's commas. VBA does not like anything by EN-US regional settings in a .Formula, .FormulaR1C1 or .FormulaArray property. If you still have trouble, use debug,print to see how the .Formula is being returned. If it contains semi-colons, then use,

With Worksheets("Sheet3")
    .Range("A2").FormulaArray = Replace(.Range("b2").Formula, Chr(59), Chr(44))
End With

Runtime Error 1004 Copy Formula Array

1 Answers