excel vba index match array to separate file

Question

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

Alvaro CC · Accepted Answer · 2019-06-07 20:37:31Z

0

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

answered Jun 7, 2019 at 20:37

Alvaro CC

1426 bronze badges

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

3 Comments

user11615804 Over a year ago

Yes, I recorded the excel formulas using the ctrl-shift-enter to tell excel it was an array formula instead of a standard formula and was getting valid results. it only became a problem when I tried to run the vba code. Thank you

Alvaro CC Over a year ago

Ok , so when you run the macro you have something like myrange.select?

user11615804 Over a year ago

Its more like Range(Cells(TRow, DCol), Cells(BRow,DCol)).select where i've had the macro find based on criteria top row (TRow), the bottom row (BRow) and the data column (DCol). And i dont actually have it select. Its just Range(...).FormulaArray. i have tested and found that using .select followed by selection.FormulaArray doesnt change the error. Thank you -SR

Collectives™ on Stack Overflow

excel vba index match array to separate file

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related