FormulaArray getting error in VBA

[LloydFinancials]

When I run this formula as activecell.formula in VBA, then go to the cell and press Shift+Ctr+Enter, it creates an array formula as expected:
=IF(LEN(A13),SUM(SUMIF(INDIRECT(VLOOKUP($B$3,Combos!A:C,2,0)),INDIRECT("'Accounts (2)'!"&ADDRESS(1,MATCH(A13,'Accounts (2)'!$1:$1,0))&":"&ADDRESS(COUNTA(INDIRECT("'Accounts (2)'!"&LEFT(ADDRESS(1,MATCH(A13,'Accounts (2)'!$1:$1,0),4),1)&":"&LEFT(ADDRESS(1,MATCH(A13,'Accounts (2)'!$1:$1,0),4),1))),MATCH(A13,'Accounts (2)'!$1:$1,0))),INDIRECT(VLOOKUP($B$3,Combos!A:C,3,0)))),)

When I run the same formula as activecell.formulaarray I get "Runtime Error 1004: Unable to set the FormulaArray property of the Range class"

Microsoft Office Professional Plus 2013
Windows 10

Please advise.

Thanks,

Luke

 

[RoryA]

The FormulaArray property is limited to 255 characters.

At first glance it looks as though you could use SUMPRODUCT rather than SUM and skip array-entry. I'd also suspect that using a table or dynamic named range would remove the need for all those INDIRECT calls.

 

[LloydFinancials]

I think you may be right, trying to build the logic with a sumproduct but not seeing a solution yet. Here is a link to the workbook. Formula is in Recon!E:13.

SPR201183 Project 310061.02x.xlsx

Recon ACCOUNT RECONCILIATION Account No.,SPR201183 Project 310061.02,Dept:,163500,Accountant - ------------------------------------------------------- Budget Period:,9/1/2020 - 8/31/2021,P.I.,Sponsor: ------------------------------------------------------- Account Name,Poison Control Stabilizat...

docs.google.com

 

[LloydFinancials]

I think I got it, had to change my Accounts table to 2 columns and add a couple helper columns to the GER.