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
?
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