Formula Array Index Match w/ Variable Filename - Run-time Error '1004'

[jobell]

Good evening all,

I'm getting a runtime error stating unable to set the formulaarray property of the Range class.

I need this to enter an index match formula in wb1 AF2 that returns a value from wb2 by matching 4 different criteria. What am I doing wrong?

Please Login or Register  to view this content.

[6StringJazzer]

I can't see an obvious problem but that's a tricky formula to read.

I suggest you declare a variable, put the formula in the variable, then print it before trying to assign it to a cell. Then show us the finished formula as it is printed in the Immediate window.

Please Login or Register  to view this content.

[jobell]

Thanks, 6StringJazzer!

I did try running this code with the actual file name in place of the variable wb2 in the formula line of code and it does work. I'm sure there is a cleaner way to do a multiple variable index/match but I'm pretty green with VBA.

I just tried running this and I'm getting the same error on the last line of code.

Attachment 817108

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl check in wb2 sheet "StopTimeDetails" is existing or the name is not exactly matching (Eg: like extra space or missing space). Except that there is no other problem.

[jobell]

Solved the issue. My referenced file name for wb2 was so long that it caused the array formula to be too long as well. I don't know what the max length for an array formula is but by reducing the file name to 15 characters, it was short enough to work.

Original file name for wb2 - WeekToDate_Stop Time Analysis_021023.xlsx
Updated file name for wb2 - WTD_021023.xlsx

Thank you all for your help.

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.