Range.FormulaArray causing error in specific cases

Question

I have a piece of code which puts an array formula in a range. It is throwing runtime error:1004 "Unable to set the FormulaArray property of the Range class". But when I paste the same formula in the cell and hit Ctrl+Shift+Enter everything works fine.

strFormula = "=IF(SUM(--(A2=Data!$A$2:$A$1423)*--(B2=YEAR(Data!$B$2:$B$1423))*  --(C2=MONTH(Data!$B$2:$B$1423)) * (Data!$E$2:$E$1423))=0,TEXT(,),SUM(--(A2=Data!$A$2:$A$1423)*--(B2=YEAR(Data!$B$2:$B$1423))*  --(C2=MONTH(Data!$B$2:$B$1423)) * (Data!$E$2:$E$1423)))"

shtAbsoluteData.Range("D2").FormulaArray  = strFormula

The problem occurs when the IF condition was inserted. So without the IF the following code works fine:

strFormula = "=SUM(--(A2=Data!$A$2:$A$1423)*--(B2=YEAR(Data!$B$2:$B$1423))*  --(C2=MONTH(Data!$B$2:$B$1423)) * (Data!$E$2:$E$1423))"

shtAbsoluteData.Range("D2").FormulaArray = strFormula

Note: If I use shtAbsoluteData.Range("D2").Formula then there is no error but the result is incorrect

You don't need all of those double unaries if you are multiplying the boolean conditions against each other. The act of multiplication does the same thing. — user4039065
– user4039065, Commented Mar 10, 2016 at 16:14

Community · Accepted Answer · 2017-05-23 11:59:31Z

2

A Range.FormulaArray property can only have 255 characters and yours was showing 248. A few mistypes might have brought it over the limit.

strFormula = "=IF(SUM((A2=Data!$A$2:$A$1423)*" & _
                     "(B2=YEAR(Data!$B$2:$B$1423))*" & _
                     "(C2=MONTH(Data!$B$2:$B$1423))*" & _
                     "(Data!$E$2:$E$1423))," & _
                 "SUM((A2=Data!$A$2:$A$1423)*" & _
                     "(B2=YEAR(Data!$B$2:$B$1423))*" & _
                     "(C2=MONTH(Data!$B$2:$B$1423))*" & _
                     "(Data!$E$2:$E$1423))," & _
                 "TEXT(,))"   '<~~ 226 characters
shtAbsoluteData.Range("D2").FormulaArray = strFormula

As I mentioned in comments, the double-unary operator is not necessary when multiplying boolean conditional statements against each other. The act of the mathematical operation makes the boolean ► numerical conversion.

Alternatives

In Excel Options, Advanced, Display options for this worksheet you can turn off Show a zero in cells that have zero value.

Any of the Accounting number formats will display a hyphen in place of a zero value.

A custom Number Format Code can be generated that simply does not display zeroes at all. One of the simplest would be,

General;General;;@

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Mar 10, 2016 at 16:27

user4039065

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

1 Comment

user4039065 Over a year ago

Have you considered simply not displaying a zero rather than running that array formula twice just to get a zero-length string in place of a zero?

Maxter · Accepted Answer · 2020-01-28 17:24:47Z

0

I got the same problem. What I did to solve the problem is to first declare the range object, then use .FormulaArray on the range.

Using C#:

Microsoft.Office.Interop.Excel.Range eRange = wks.Range[wks.Cells[1,1], wks.Cells[20, 20]];
eRange.FormulaArray = $@"=some formula";

answered Jan 28, 2020 at 17:24

Maxter

8868 silver badges21 bronze badges

Collectives™ on Stack Overflow

Range.FormulaArray causing error in specific cases

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related