Don't loop:

Range("D2").FormulaArray = "=MAX(IF(R2C1:R6C1=RC1,R2C2:R6C2,FALSE))"
Range("D2").AutoFill Destination:=Range("D2:D" & r), Type:=xlFillDefault


--

HTH,
Bernie
MS Excel MVP


"Amedee Van Gasse" wrote in message
...
Excel 2007.

I found a bottleneck in my code, in this part:


r = shDAT.UsedRange.Rows.Count
For i = 2 To r
shDAT.Cells(i, 4).FormulaArray = _
"=MAX(IF(R2C1:R" & r & "C1=RC1,R2C2:R" & r &
"C2,FALSE))"
Application.StatusBar = Format(i / r, "0.0%") '
Added to see the progress
Next


Currently the value of r is 13693.
Screenupdating & calculation are already disabled.

I know from literature that extensive use of array formulas can slow
down a sheet, and now I experienced it first hand.

What suggestions or strategies do you suggest to speed this up? I
suppose that a rewrite of the logic of this part is needed?

--
Amedee