CODEWITHSUNDEEP
arraysexcelvbaindexingmatch
user11615804
user11615804

Reputation: 1

excel vba index match array to separate file

I have recorded an index match array formula (multiple criteria columns) and when I Play it, it fails...

"Run-time error 1004: Unable to set the FormulaArray property of the Range class"

with excel vba, I can write an index match array formula that references a table or worksheet in the same workbook but when I try and reference a large database table that is saved in a separate file, I get the 1004 error mentioned in the title.

'I can do this index match array formula which references worksheets within the same workbook

Selection.FormulaArray = "=Index('GL - Chart of Accts Listing'!R6C1:R5000C1,Match(RC3&""*Salary*"",'GL - Chart of Accts Listing'!R6C1:R5000C1&'GL - Chart of Accts Listing'!R6C2:R5000C2,0))"

'End result is:

'{=INDEX('GL - Chart of Accts Listing'!$A$6:$A$5000,MATCH($C12&"*Salary*",'GL - Chart of Accts Listing'!$A$6:$A$5000&'GL - Chart of Accts Listing'!$B$6:$B$5000,0))}

'This formula however gives me the runtime 1004 error even though it is exactly how I recorded it.

Selection.FormulaArray = "=INDEX('[All AP Transactions by Account Number - CMS Table GLSBAP.xlsm]Data File'!R1C5:R47948C5,MATCH(RC4&RC14,'[All AP Transactions by Account Number - CMS Table GLSBAP.xlsm]Data File'!R1C2:R47948C2&'[All AP Transactions by Account Number - CMS Table GLSBAP.xlsm]Data File'!R1C10:R47948C10,0))"

Any assistance is greatly appreciated. Thanks, SR

Upvotes: 0

Views: 269

Answers (1)

Alvaro CC
Alvaro CC

Reputation: 142

Did you select the right range before running the line?
Selection.FormulaArray = "=INDEX('[All AP Transactions by Account Number...

Upvotes: 0

Related Questions