VBA to fill a range with an Array Formula

[gheyman]

Is there a way to fill a range with an Array formula. I need the formula going into column R to be an Array

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("Q3:Q11").FormulaR1C1 = _
        "=IF(AND(INDEX(C25,MATCH(RC15,C[7],0))=TRUE,COUNTA(R3C22:R230C22)=0),RC[-3],IF(AND(INDEX(C25,MATCH(RC15,C[7],0))=TRUE,COUNTIF(R3C22:R230C22,RC[-1])>0),RC[-3],""""))"
    Range("R3:R11").FormulaR1C1 = _
        "=IFERROR(INDEX(R2C17:R248C17,SMALL(IF(ISTEXT(R2C17:R248C17),ROW(R1C17:R247C17)), ROW(R[-1]C[-1]))),"""")"

End Sub

 

[Fluff]

Try

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("Q3:Q11").FormulaR1C1 = _
        "=IF(AND(INDEX(C25,MATCH(RC15,C[7],0))=TRUE,COUNTA(R3C22:R230C22)=0),RC[-3],IF(AND(INDEX(C25,MATCH(RC15,C[7],0))=TRUE,COUNTIF(R3C22:R230C22,RC[-1])>0),RC[-3],""""))"
    Range("R3").FormulaArray = _
        "=IFERROR(INDEX(R2C17:R248C17,SMALL(IF(ISTEXT(R2C17:R248C17),ROW(R1C17:R247C17)), ROW(R[-1]C[-1]))),"""")"
   Range("R3:R11").Filldown
End Sub

 

[gheyman]

THANK YOU! That worked.

 

[Fluff]

You're welcome

 

[gheyman]

Fluff

That array formula is supposed to make a continuous list (no blanks). I am having a problem making that list. In the first half of the Code, its doing a copy and Past value. Taking the formulas out of the column because the Array formula doesn't see formulas or " "" " as blanks. So I thought I would take the formulas out by doing a paste value. Unfortunately, it didn't work. It thinks there is still values in those cells. If I go into the "Blanks" and hit delete, the Array works. How do I get those " "" " cells to be blank?

 

[Fluff]

How about

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("Q3:Q11").FormulaR1C1 = _
        "=IF(AND(INDEX(C25,MATCH(RC15,C[7],0))=TRUE,COUNTA(R3C22:R230C22)=0),RC[-3],IF(AND(INDEX(C25,MATCH(RC15,C[7],0))=TRUE,COUNTIF(R3C22:R230C22,RC[-1])>0),RC[-3],""""))"
    Range("R3").FormulaArray = _
        "=IFERROR(INDEX(R2C17:R248C17,SMALL(IF((R2C17:R248C17<>""""),ROW(R1C17:R247C17)), ROW(R[-1]C[-1]))),"""")"
   Range("R3:R11").Filldown
End Sub