Array Formula via VBA with or without .ArrayFormula For Dynamic Range

Question

I am trying to translate a procedure previously done without VBA on an Excel sheet.

This inserts an array formula:

={MAX(IF(C2:C355=C2,F2:F355))} into cell CE2

and drag it down to the bottom of the data set, which is variable.

I have attempted different options looping through a variable data set, such as:

Dim i As Variant
LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

    Cells(i, 83).FormulaArray = "=MAX(IF(cells(2,LastRow)= cells(5,i),cells(2,LastRow))"

Next i

The following code seems to work, but my attempts to work the code into a dynamic loop causes errors:

Range("CE2").FormulaArray = "=MAX(IF(C:C=C2,F:F))"

Often the error is:

"Unable to set the FormulaArray property of the range class".

I have noticed that array formulas placed onto the sheet via VBA are slow. I am guessing that there is a way to achieve the same result as my formula via VBA that does NOT use .VarriantArray.

I have looked into the MAX function.

How do I

Loop through a dynamic array and place my array formula on the sheet?
Achieve the same result as my array formula using VBA functions other than .ArrayFormula?

Scott Craner Scott Craner · Accepted Answer · 2017-11-30T21:33:51

This without the loops using FillDown:

Dim LastRow As Long
With Worksheets("Sheet1")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).row
    .Cells(2, 83).FormulaArray = "=MAX(IF(" & .Range(.Cells(2, 3), .Cells(LastRow, 3)).Address(1, 1) & "=C2," & .Range(.Cells(2, 6), .Cells(LastRow, 6)).Address(1, 1) & "))"
    .Range(.Cells(2, 83), .Cells(LastRow, 83)).FillDown
End With

Array Formula via VBA with or without .ArrayFormula For Dynamic Range

2 Answers