Range.ArrayFormula 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:438 "Object doesn't support this property or method"

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").ArrayFormula  = 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").ArrayFormula = strFormula

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

Dirk Reichel · Accepted Answer · 2016-03-10 13:19:35Z

0

You can't enter strings with a length of more than 255 characters directly via vba as like in your case. Please use the Replace-trick for such cases.

answered Mar 10, 2016 at 13:19

Dirk Reichel

7,9991 gold badge18 silver badges31 bronze badges

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

Comments

Kaper · Accepted Answer · 2016-03-10 13:21:26Z

0

The code shall probably read:

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, """" ,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)))"

it should help if it is Text with empty arguments what makes problem.

PS. Note doubled quotation marks

answered Mar 10, 2016 at 13:21

Kaper

112 bronze badges

Collectives™ on Stack Overflow

Range.ArrayFormula causing error in specific cases

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related