Converting Array Formula into VBA returns an error

[HJHamm]

I have looked around the web and it appears if my array formula is too long to run inside VB. I am getting the "Unable to set the formulaarray Property" error message
Unfortunately I have no clue how to re-write this in order for it to work
The Formula is:

Please Login or Register  to view this content.

The idea is to the formula to run and the procedure to finish up in the following manner

Please Login or Register  to view this content.

Any assistance is GREATLY appreciated!

[Leith Ross]

Hello HJHamm,

Can you decrypt what this array formula is doing in words?

[HJHamm]

Leith I will do the best I can!
I have 3 levels of hiearchy I have to check represented by the if statement checking to see if the cells in question in column D are blank.
If level 3 (D4) is blank and level 2 (D3) is blank then I need to find the store number in Column A on Sheet called Rostered by Matching the the level 1 Regional store sort order (Column P:P and cell A11) as well as the Region Name (Column H:H and cell D2)... now working this backwards to level 2 I am indexing the same set of store numbers in Column A, but matching it by different criteria; Area Store Sort Order (Column O:O and cell A11) as well as the Area Name (Column K:K and cell D3). Lastly, indexing the same set of store numbers, but this time I need to match it by Territory Store Sort Order (Column N:N and cell A11) and Territory Name (Column D:D and cell D4).
Does this help explain it any better?

[shg]

As a hack, your code could rename worksheet Rostered to just R, insert the formula ...

=IF(D$4="", IF(D$3="",
INDEX(R!A:A, MATCH(1, R!P:P=Worksheet!A11 * R!H:H=Worksheet!D$2,0)),
INDEX(R!A:A, MATCH(1, R!O:O=Worksheet!A11)*(R!K:K=Worksheet!D$3),0)),
INDEX(R!A:A, MATCH(1, R!N:N=Worksheet!A11 * R!D:D=Worksheet!D$4,0)))

Then change it back.

[HJHamm]

shg... unfortunately the hack doesn't work, it appears as I am still over the max...

[shg]

Not by my reckoning; with the change, it's 234 characters, and the limit is 255.

[HJHamm]

Finally after several attempts I have it working now... SHG used your idea and kept trimming things down. I would like to keep this open for awhile longer in order to see if anyone can tell me a "straight VBA" line of code that will do the same thing as I may very well run into this situation again and a "qucik fix" will not work... Say for example is I had to do 4 index(match)(match) I would be in trouble

[shg]

Here is a function that inserts long array formulas.

Please Login or Register  to view this content.

Here's an example that demonstrates:

Please Login or Register  to view this content.