Applying array formula to range VBA

[Leila33]

Hello,

I am trying to apply the following array formula to a range in Excel:

=IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF(AllFiles!$C$2:$C$1000000=$A$2,ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),"")

using the following methods that I found online (I'm not fluent in VBA)...

Sub filldowntest()


ActiveSheet.Range("D2:G2").FormulaArray = "=IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF(AllFiles!$C$2:$C$1000000=$A$2,ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),"")"

ActiveSheet.Range("D2:G10000").FillDown


End Sub


I keep getting the 1004 error, produced when FormulaArray uses more than 255 characters...yet my formula is within these characters. I even tried the Replace method but that didn't work either.

Anyone know how to solve this?

I also need the cells to show only the results, by the way, not the formula...Thank you very much.

 

[Norie]

Double up the quotes in the formula.

Sub filldowntest()


    ActiveSheet.Range("D2:G2").FormulaArray = "=IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF(AllFiles!$C$2:$C$1000000=$A$2,ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),"""")"

    ActiveSheet.Range("D2:G10000").FillDown


End Sub

 

[Leila33]

Thank you!

I am now running into the following problem though...I am getting the same result in all cells. In other words, the unlocked cells in the formula are not changing as the code drags them...

 

[Norie]

Which references aren't changing?

Which should change?

 

[Leila33]

everything bold and underlined:

ActiveSheet.Range("D2:G2").FormulaArray = "=IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF(AllFiles!$C$2:$C$1000000=$A$2,ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),"""")"

ActiveSheet.Range("D2:G10000").FillDown