LloydFinancials
Well-known Member
- Joined
- Apr 24, 2015
- Messages
- 546
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
=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