vendredi 14 décembre 2018

How can I make For loops with an If statement more efficient when iterating over rows in VBa?

I have a for loop nested inside a for loop which iterates over every row in a spreadsheet.

The nested for loop below checks the current row and then loops over every row to in the spreadsheet to see if it matches criteria in the If statement. If so it changes a bool to True and exits the nested loop.

This method takes far too long. The spreadsheet is 1000 rows x 27 columns and will take forever to run through on the small PC i'm using.

    Dim duplicateData As Boolean
    duplicateData = False

    For j = 2 To 300
        If ((Data.Cells(i, 19) < Data.Cells(j, 19) + (Data.Cells(j, 20) / 1440) + (Data.Cells(j, 21) / 1440) _
        And Data.Cells(i, 19) >= Data.Cells(j, 19)) _
        Or _
        (Data.Cells(i, 19) + (Data.Cells(i, 20) / 1440) + (Data.Cells(i, 21) / 1440) <= Data.Cells(j, 19) + (Data.Cells(j, 20) / 1440) + (Data.Cells(j, 21) / 1440) _
        And Data.Cells(i, 19) + (Data.Cells(i, 20) / 1440) + (Data.Cells(i, 21) / 1440) > Data.Cells(j, 19))) _
 _
        And Data.Cells(i, 18) = Data.Cells(j, 18) _
        And Data.Cells(i, 22) = Data.Cells(j, 22) _
        And Not i = j Then

            duplicateData = True
            Exit For

        End If
    Next j

The data

ClinicalTime and AdminTime are in minutes and need to be divided by 1440 before being added to Time to get the correct finish time.

James sees someone at 13:00 on the 12th Jan and finishes at 13:30. But it also shows he saw someone at 13:25 which isn't possible as he was with someone during that time.

The above code will change duplicateData to True for both these rows, but will take a very long time to do so over thousands of these instances.

Columns 18        19     20             21            22
        Date      Time   ClincialTime   AdminTime     Clinician
        12/01/18  13:00  20             10            James
        12/01/18  13:25  10             20            James
        12/01/18  14:30  40              0            James
        14/01/18  10:00  20             20            Samantha 

Worth noting is a finish time can be the same as a start time, so James could see a patient at 11:00, finish at 11:30, and have start time for the next patient at 11:30 and there would be no need to flag these two.

Aucun commentaire:

Enregistrer un commentaire