FormulaArray throws an error with correct array formula

Question

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.

TomJohn · Accepted Answer · 2018-02-02 10:04:35Z

2

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.

edited Feb 2, 2018 at 10:04

answered Feb 2, 2018 at 9:46

TomJohn

7476 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AntiDrondert Over a year ago

I used russian formulas, just pretend that all ; you find are actualy , ^^'. My formulas actualy look something like this "=ЕСЛИ(" & АдресТекущий & ">0;1;0)"

TomJohn Over a year ago

Changing that makes code work flawlessly for me. In VBA you should use English formula names like you did ("IF") and "," as a formula separator (I have Polish excel and ";" as seprator in excel as well :) )

AntiDrondert Over a year ago

You should change an answer to your comment and I will accept it, I just used english formulas and it worked, formula separators weren't an issue in the end.

Foxfire And Burns And Burns Over a year ago

@tomjohnriddle is right. When you use VBA, your formulas must be in English. If you want to use your language formulas, check the property FormulaLocal

AntiDrondert Over a year ago

@FoxfireAndBurnsAndBurns +1 I would use FormulaLocal but I needed an array formula.

Collectives™ on Stack Overflow

FormulaArray throws an error with correct array formula

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related