VBA - Long Array Formula conundrum

[DanielBoon82]

I apologize as I know this has been breached a bit but I can not find anything on this site or others that references my issue.

I know that a Long Array needs

• to be assembled with a with statement
• that the formula needs to be 255 chars (each portion to be assembled)
• "complete" (on its own) when you assign it to a variable to be combined.

On to the question, is there anyway or a source of information that anyone knows that references building a long array formula that can't be complete on its own?

The array that I am trying to build uses IFNA INDEX MATCH (multiple match conditions) in a different workbook. While I look at the formula I can't see how to tear it down into multiple tiers "complete on its own"

I have been doing a lot of reading www.cpearsons/com/arrayformulas.aspx various books and sites dealing with arrays and I can not find a method that works for what I have.


Thank you

Snippet

=IFNA(INDEX([PTA_Setup.xlsm]Compiled!$F$2:$F$175000,MATCH(D3&G3&1,[PTA_Setup.xlsm]Compiled!$B$2:$B$175000&[PTA_Setup.xlsm]Compiled!$C$2:$C$175000&IF(K3>=[PTA_Setup.xlsm]Compiled!$D$2:$D$175000,IF(K3<=[PTA_Setup.xlsm]Compiled!$E$2:$E$175000,1)),0)),INDEX([PTA_Setup.xlsm]RS_RSR_WORKING!$H$2:$H$87295,MATCH(D3&G3&1,[PTA_Setup.xlsm]RS_RSR_WORKING!$A$2:$A$87295&[PTA_Setup.xlsm]RS_RSR_WORKING!$B$2:$B$87295&IF(K3>=[PTA_Setup.xlsm]RS_RSR_WORKING!$C$2:$C$87295,IF(K3<=[PTA_Setup.xlsm]RS_RSR_WORKING!$D$2:$D$87295,1)),0)))

 

[NARAYANK991]

Hi ,

Check out the following search results :

https://chandoo.org/forum/threads/vba-long-array-formula.15957

https://chandoo.org/forum/threads/formulaarray-replace-assistance.29303

If you want more help , please post a complete formula , as you would enter it in a worksheet cell.

Looking at the formula you have posted , one simple reason is that your filename and worksheet name are the culprits ; if you can change them to smaller names , you can bring the formula down within the 255 character limit. Once it has been entered in the worksheet , you can replace the smaller names with the original , longer / more descriptive names.

Narayan

 

[Debaser]

For a formula like that, you have two sections:

=IFNA(formula1,formula2)

so you need to replace one, or both, of the formulas with a placeholder to keep the length down while using formulaarray, then replace the placeholders with the actual formula sections.