setting FormulaArray slows down my code
On 1 apr, 14:45, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Don't loop:
Range("D2").FormulaArray = "=MAX(IF(R2C1:R6C1=RC1,R2C2:R6C2,FALSE))"
Range("D2").AutoFill Destination:=Range("D2:D" & r), Type:=xlFillDefault
Thanks!
The code is now:
r = shDAT.UsedRange.Rows.Count
shDAT.Range("D2").FormulaArray = "=MAX(IF(R2C1:R" & r &
"C1=RC1,R2C2:R" & r & "C2,FALSE))"
shDAT.Range("D2").AutoFill Destination:=shDAT.Range("D2:D" & r),
Type:=xlFillDefault
Execution of this part of the code is now a magnitude faster, and it
produces exactly the same result.
Calculation at the end of the code (when calculation is set to
xlCalculationAutomatic again) is of course still slow, but this is
expected.
One does not simply walk into 10K array formulas and expect it to be
fast. ;-)
|