Search code examples
excelvba7

Setting Array Formula doesn't SPILL to other cells (I'd like it to do so)

When I set an array formula, the formula doesn't Spill into the other cells the way I like it to. Usually, it goes to just one cell, hiding most of the presentation, but other times it seems to work. I can't understand what's happening. And ideas?

Here's some sample code:

        ws.Range("A12").Formula = "=INDEX(SORT(FILTER(Data!B7:BI2011, (Data!D7:D2011<>"""")*(Data!G7:G2011=""Non-Instructional"")), 60, -1), SEQUENCE(5), {3,6,60})"
Solution

  • You have to use Formula2 to make use of spill-functionality:

    ws.Range("A12").Formula2 = "=INDEX(SORT(FILTER(Data!B7:BI2011, (Data!D7:D2011<>"""")*(Data!G7:G2011=""Non-Instructional"")), 60, -1), SEQUENCE(5), {3,6,60})"