Converting ArrayFormula to VBA

[Rubsie]

Hi,

I have this working arrayformula in excel (only one sheet in the .xls):

{=SUM(IF((C5:C25<3);G5:G25;0))-SUM(IF((C5:C25<3)+IF(ISNUMBER(SEARCH("*flat*";I5:I25;1));1;0)=2;G5:G25;0))}

What I want to do now is to execute this array formula using VBA and store the result in cell A1.

I tried this code:

Range("A1").FormulaArray = "=SUM(IF((C7:C27<3);G7:G27;0))-SUM(IF((C7:C27<3)+IF(ISNUMBER(SEARCH(""*flat*"";I7:I27;1));1;0)=2;G7:G27;0))"

But with this, I always get the Runtime 1004 error.

Can anyone help me solve this problem?

 

[Richard Schollar]

Hi

Try it with commas instead of semi-colons:

Range("A1").FormulaArray = "=SUM(IF((C7:C27<3),G7:G27,0))-SUM(IF((C7:C27<3)+IF(ISNUMBER(SEARCH(""*flat*"",I7:I27,1)),1,0)=2,G7:G27,0))"

 

[RoryA]

Incidentally, you could shorten your formula to:
=SUMPRODUCT(--(C5:C25<3);--(ISERROR(SEARCH("*flat*";$I$5:$I$25)));G5:G25)

which does not need to be array-entered.

 

[Rubsie]

Hi

Try it with commas instead of semi-colons:

Range("A1").FormulaArray = "=SUM(IF((C7:C27<3),G7:G27,0))-SUM(IF((C7:C27<3)+IF(ISNUMBER(SEARCH(""*flat*"",I7:I27,1)),1,0)=2,G7:G27,0))"

Thanks Richard, works great!