Long array formulas in VBA...

[Matty]

Hi,

I have tried the solution detailed in the attached link:

Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA

But I can't seem to adapt it to work for my situation.

The formula I am using is such that it can't be slimmed down sufficiently to fall inside of the 255 character limit.

My formula is in excess of 600 characters, so is there a way of doing utilising this solution with multiple replaces?

Thanks,

Matty

 

[philipq]

Hello pgc01,

Kindly could you advise on how to split the following formula with place holders.
I just can't get my head around it.

        "=IFERROR(INDEX('Sales Detailed'!G:G,SMALL(IF(('Sales Detailed'!$D$2:$D$" & salesCount & "=""TBR"")+('Sales Detailed'!$F$2:$F$" & salesCount & "=$A2)+('Sales Detailed'!$AG$2:$AG$" & salesCount & "<>""Export"")+('Sales Detailed'!$AG$2:$AG$" & salesCount & "<>""Demo"")+('Sales Detailed'!$AG$2:$AG$" & salesCount & "<>""Courtesy"")=5,ROW('Sales Detailed'!$G$2:$G$" & salesCount & ")),COUNTIF(A$2:A2,A2))),""No Model Found In Sales List"")"

Thanks,
Philip