CODEWITHSUNDEEP
vbaexcel
AntiDrondert
AntiDrondert

Reputation: 1149

FormulaArray throws an error with correct array formula

MyData.Columns("R").Address is $R$10:$R$121
AddressCurr is $D$10:$D$121
AddressPr is $O$10:$O$121
MyData.Columns(5).Address is $E$10:$E$121

AddressPr = MyData.Columns("O").Address
AddressCurr = MyData.Columns("D").Address
MyData.Columns("R").FormulaArray _
    = "=IF(" & AddressCurr & ">0;1;0)"

Third line throws:
Run-time error '1004' Unable to set the formulaarray property of the range class

Whenever I input formula =IF($D$10:$D$121>0,1,0) on a sheet manualy, it works as intended.

Why does Run-time error keeps popping out when populating range with correct array formula?

Note: This formula is a part of a bigger one =ROUND($O$10:$O$121/IF($D$10:$D$121=0,1,$D$10:$D$121)*$E$10:$E$121,-1) 

MyData.Columns("R").FormulaArray _
    = "=" & AddressPr & "/" & _
            "IF(" & AddressCurr & "=0,1," & AddressCurr & ")*" & _
            "" & MyData.Columns(5).Address & ""

It throws an error as well.

On the contrary following formula works like a charm:

MyData.Columns("R").FormulaArray _
    = "=" & AddressPr & "/" & _
        "" & AddressCurr & "*" & _
        "" & MyData.Columns(5).Address & ""

My guess is that it can't form a boolean array, but following doesn't work with ROUND either.

Upvotes: 1

Views: 141

Answers (1)

TomJohn
TomJohn

Reputation: 747

Change 

MyData.Columns("R").FormulaArray = "=IF(" & AddressCurr & ">0;1;0)"

to

MyData.Columns("R").FormulaArray = "=IF(" & AddressCurr & ">0,1,0)"

In VBA you should use , as a separator even if you have different separator in your Excel.

Additionaly, please note that in VBA you should use English formula names and not your local ones when using .Formula or .FormulaArray.

If you would like to use your local language formulas you could use .FormulaLocal however, it does not seem that there is array version of .FormulaLocal (indirect solution: How to combine FormulaArray and FormulaLocal options in Excel VBA?). Please notice that using .FormulaLocal requires you to use your local separator.

Upvotes: 2

Related Questions