Optimize Loop for speed 1

[osx99]

I have some vba code in excel which runs on the Worksheet_Change event after an update to the worksheet every 200ms

The refresh rate after running existing code averages approx 210ms however when I add this code to compare two ranges the average refresh rate inreases to approx 400ms

What is the absolute optimum way of speeding up my code to compare cells in 2 ranges and update upon conditions. My current code which slows this down to 400ms is

Application.EnableEvents = False
Application.ScreenUpdating = False

CurrentPRICE = Range("h5:h55")
Set LowestPRICE = Range("ay5")
        
   r1 = 0
   For Each c1 In CurrentPRICE
                 
   If c1 < LowestPRICE.Offset(r1, 0).Value Or   LowestPRICE.Offset(r1, 0).Value = "" Then
    LowestPRICE.Offset(r1, 0) = c1

          End If

    r1 = r1 + 1
    Next c1
Application.ScreenUpdating = True
Application.EnableEvents = True

i want to compare cell h5 to ay5 and if h5 is lower set ay5 to h5 then repeat for h6 and ay6 etc

Many thanks
Os

 

[PHV]

What about this ?

Application.EnableEvents = False
Application.ScreenUpdating = False
Dim r As Long
With ActiveSheet
  For r = 5 To 55
    Select Case .Cells(r, 51).Value
    Case "", Is > .Cells(r, 8).Value
      .Cells(r, 51).Value = .Cells(r, 8).Value
    End Select
  Next
End With
Application.ScreenUpdating = True
Application.EnableEvents = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[osx99]

Wow, that seems to have worked a treat. Average refresh is now between 210ms to 220ms

Thank you
Os

 

[osx99]

It occured to me that it may be even quicker to read the ranges into an array calculate and pass the result back out to a range. eg.

  Sub test()
    Dim strArray As Variant, rng As Range
    Set rng = Range("a2:d14")
    strArray = rng
    For i = 1 To UBound(strArray)
    strArray(i, 4) = strArray(i, 1) - strArray(i, 3)
    Next i
    Range("f2:f14").Value = Application.WorksheetFunction.Transpose(strArray)
  End Sub

Is it possible to pass out just one element of the array using Range("f2:f14").Value = Application.WorksheetFunction.Transpose(strArray) so I pass out strArray(i, 4) but without a for next loop?

WOuld it be just as quick to create a second array to store the value only?

 

[osx99]

It's okay I've figured this one out

Range("f5:f55").Value = Application.WorksheetFunction.Index(strArray, 0, 4)

 

[osx99]

One last question on this

I can write a single dimension of an array to an excel range using

Range("y1:y55").Value = Application.WorksheetFunction.Index(racedetails, 0, 25)

how do I pass a partial array to a range so that origianl array is range("a1:ad55") and I want to pass just the y1:ad55 element range back again after some updates to the array

Range("y1:ad55").Value = Application.WorksheetFunction.Index(racedetails, 0, 25 to 30)