Loop Gets Slower

M40wen · Mar 27th 2017

Hi all,

I have looked around the net, but cant seem to find a "reason" as to why a loop gets slower... The first 10,000 rows gets done in 3 secs, rows 80,000-90,000 takes 30 secs. Does anyone know why? I have screenupdating, displayalerts, enable events and calculation all set to false, my data is sorted as applicable, there are no objects on each loop, I use evaluate in vba, and as data is sorted any ranges are down to the minimum (basically I find the first and last row for a searched value) just cant seem to find why it slows down.

I know there may be alternatives to loops, my question is why would the loop slow down in the first place (and the way this project is going I have to use loops as requirements keep changing)? The data set is 100k rows and it runs in around 8 mins to calculate approx. 20 columns of counts, match/index etc.... Mind you them doing it manually took them 6 hours last week so still better than it was!

**rory** · Mar 27th 2017

Re: Loop Gets Slower

What's inside the loop?

M40wen · Mar 27th 2017

Re: Loop Gets Slower

Quote from rory;789048

What's inside the loop?

Nothing too horrific...

Code

Rw = 2
CheckRw = 10000Sheets("Kronos").DisplayPageBreaks = False
On Error Resume Next
Do Until Rw > eRw
Srch = Application.Match(ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 1), Range(ThisWorkbook.Sheets("Kronos").Cells(1, Ecl + 1), ThisWorkbook.Sheets("Kronos").Cells(eRw, Ecl + 1)), 0)
Srch2 = 0
Srch2 = Application.Match(ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 1).Value, Range(ThisWorkbook.Sheets("Kronos").Cells(1, Ecl + 1), ThisWorkbook.Sheets("Kronos").Cells(eRw, Ecl + 1)), 1)
If Srch2 = 0 Then Srch2 = Srch
On Error GoTo 0
CountUnq = Srch2 - Srch + 1
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 2) = CountUnq
If ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 2) = 1 Then
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 3) = ThisWorkbook.Sheets("Kronos").Cells(Rw, 16)
Else
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 3) = Application.Sum(ThisWorkbook.Sheets("Kronos").Cells(Srch, 16).Address & ":" & ThisWorkbook.Sheets("Kronos").Cells(Srch + CountUnq - 1, 16).Address)
End If


ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 4) = Month(ThisWorkbook.Sheets("Kronos").Cells(Rw, 1)) & "-" & Year(ThisWorkbook.Sheets("Kronos").Cells(Rw, 1))
If InStr(1, "Bank Holiday", ThisWorkbook.Sheets("Kronos").Cells(Rw, 14)) = 0 Then
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 5) = "N"
Else
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 5) = "Y"
End If
 


SrchTMW = 0
On Error Resume Next
SrchTMW = Application.Match(ThisWorkbook.Sheets("Kronos").Cells(Rw, 17).Value, Range(ThisWorkbook.Sheets("Team Monitoring (Weekly)").Cells(1, TMWPersonID), ThisWorkbook.Sheets("Team Monitoring (Weekly)").Cells(TMWErw, TMWPersonID)), 0)
On Error GoTo 0
If SrchTMW = 0 Then
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 7) = "N"
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 9) = "NA"
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 10) = "NA"
Else
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 7) = "Y"
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 9) = ThisWorkbook.Sheets("Team Monitoring (Weekly)").Cells(SrchTMW, TMWManager)
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 10) = ThisWorkbook.Sheets("Team Monitoring (Weekly)").Cells(SrchTMW, TMWTrade)
End If


SrchH = 0
On Error Resume Next
SrchH = Application.Match(ThisWorkbook.Sheets("Kronos").Cells(Rw, 17).Value, Range(ThisWorkbook.Sheets("Holiday").Cells(1, HPersonID), ThisWorkbook.Sheets("Holiday").Cells(HeRw, HPersonID)), 0)
On Error GoTo 0
If SrchH = 0 Then
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 8) = ""
Else
ThisWorkbook.Sheets("Kronos").Cells(Rw, Ecl + 8) = ThisWorkbook.Sheets("Holiday").Cells(SrchH, HHoursLefttoTake)
End If
 
 


If Rw = CheckRw Then
frmPBIUpdate.LblStatus = "Calculating Values. Processed " & Rw & " records"
DoEvents
frmPBIUpdate.Repaint
frmPBIUpdate.Show vbModeless
CheckRw = CheckRw + 10000
End If


Rw = Rw + 1
Loop

Display More

frmPBIUpdate is a form, all the variables are declared as longs. Every 10,000 rows seems to add 4 seconds, but im not sure why. I have to use loop for now to "get it working" - the whole procedure takes about 8 mins (the data set is 900,000 rows to start with), so its no slouch, but I cant understand why the loop is slowing down.

**rory** · Mar 27th 2017

Re: Loop Gets Slower

Why do you keep showing the form again?

I suspect your code would be a lot faster if you used arrays. (and a lot neater and easier to maintain with a couple of worksheet variables! ;))

M40wen · Mar 27th 2017

Re: Loop Gets Slower

Hi Rory,

The statusbar goes missing in excel 2016 and as im testing it through i just wanted to see what the code was up to, the userform can also go missing during run time hence the check. The person asking me for help, doesnt know what they want, doesnt really understand how it currently works, but wants me to solve it!

I use loops and match simply as specs often change when others know what they want, and i can then update the bits and pieces... Anyway, on this i just wondered why the loop slowed down, as i cant see an obvious reason for it... Just seems abit odd that the first few rows goes relatively quick!

Loop Gets Slower

Participate now!

Share

Users Viewing This Thread