CODEWITHSUNDEEP
excelvbaloops
John W
John W

Reputation: 201

Is there a better alternative than a loop to reduce processing time?

I am cycling through values on a pending data list and then updating a master list if any of those values are new. The pending list will normally have 100-200 rows of data and each row will have about 10 variables that it will need to make sure are updated on the master list. The Master list is about 10,000 rows.

I have written code that will loop through each row of the pending list, assign values to variables and then perform a find on the master list looking for a matching record and then update it accordingly. My code works fine and does exactly what I want but the processing time is about 4 minutes and that's 3 minutes and 50 seconds longer than the people that use it are willing to allow without complaining.

Is there alternative coding that I could use to help decrease processing time?

The code I have is very long and so I am not going to paste it all here but instead paste snip-its of it so you can have an idea of what I am currently doing: 

Application.Screenupdating = False
Applicaiton.Enableevents = False
Application.Calculation = xlCalculationManual

PendingBRow = ThisWorkbook.Sheets("PendingLog").Range("A65000").End(xlUp).Row
MasterBRow = ThisWorkbook.Sheets("MasterLog").Range("A65000").End(xlUp).Row

For D = 2 To PendingBRow
With ThisWorkbook.Sheets("PendingLog").Range("A" & D)
PendingRecordNumber = .Value
PendingIR = .offset(0, 5).Value
PendingVal = .offset(0, 6).Value
End With

With ThisWorkbook.Sheets("MasterLog").Range("B2:B" & MasterBRow)
Set c = .Find(PendingRecordNumber, LookIn:=xlValues)
If Not c Is Nothing Then
        firstAddress = c.Address
        Do
DaysSinceLastWorkedStatic = c.offset(0, 22).Value


MasterIRValue = c.offset(0, 16).Value
            If PendingIR <> 0 Then
            If PendingIR <> MasterIRValue Then
            c.offset(0, 16).Value = PendingIR
            DaysSinceLastWorkedStatic = 0
            c.offset(0, 22).Value = DateVal
            End If
            End If

            c.offset(0, 24).Value = POorLA
            c.offset(0, 25).Value = FinalizedFlag
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress ' in rare cases a record number could be duplicated on the master list.  


end if
end with

ThisWorkbook.Sheets("PendingLog").Range("A" & D).offset(0, 15).Value = DaysSinceLastWorkedStatic
Next D

One alternative I had considered was to filter the master list by the record number and update the visible row only and then unfilter for each record number on the pending list. I have not yet tested this method.

Could this method be better than what I have?

Upvotes: 0

Views: 904

Answers (1)

user3271518
user3271518

Reputation: 628

So I dont know if this will be an answer but I think this might at least give you a look at another type of find/update code.

This is used to update the times people are scheduled to work

In this example I have a Row A with Names B with Times. I paste the updated Name and Time in rows H and I. Some times its all 98 some times it just 5 and this program looks at the list finds the name and takes the time from I and writes it over its corresponding time in B.

Option Explicit
Sub Update_Holiday()

Dim ws          As Worksheet
Dim SrcRng      As Range
Dim schRng      As Range
Dim c           As Range
Dim search      As Range


Set ws = ThisWorkbook.Sheets(3)
Set SrcRng = ws.Range("H2:H98")
Set schRng = ws.Range("A2:A98")


For Each c In SrcRng
    Set search = schRng.Find(c.Value, LookIn:=xlValues, SearchDirection:=xlNext)
    If Not search Is Nothing Then
        c.Offset(, 1).Copy search.Offset(, 1)

    End If
Next c

End Sub

Upvotes: 3

Related Questions