vendredi 26 juin 2020

VBA - How to make code with For Loops and IF Statements more efficient?

I wrote a code to match data (MaterialPN vs. MaterialPS and WeekPN vs. WeekPS) and copy appropriate values between two sheets (Packaging Needs - PN and Packaging Staging - PS).

I already turned off ScreenUpdating, Calculations and Events. This made the run time go from 5 minutes to 1 minute, which is still quite slow (my data is ~3000 rows only). I also tried forcing an exit of the If Statement when the WeekPN is not equal to WeekPS with the use of GoTo Flag1, but this did not make my code run any faster.

Any tips on how to make this code more efficient?

Thanks in advance for any help!

Sub PackagingNeeds2PackagingStaging()
       
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

With Sheets("Packaging Needs")
i = .Cells(.Rows.Count, 5).End(xlUp).Row
End With

With Sheets("Packaging Staging")
l = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

j = 25
    For k = 9 To i
        For x = 5 To l
            For Z = 14 To j
            
                MaterialPN = Sheets("Packaging Needs").Cells(k, 5).Value
                MaterialPS = Sheets("Packaging Staging").Cells(x, 1).Value

                WeekPN = Sheets("Packaging Needs").Cells(4, Z).Value
                WeekPS = Sheets("Packaging Staging").Cells(x, 12).Value
                
                If WeekPN <> WeekPS Then GoTo Flag1
                    If WeekPN = WeekPS Then
                        If MaterialPN = MaterialPS Then
                            Sheets("Packaging Staging").Cells(x, 19).Value = Sheets("Packaging Needs").Cells(k, Z).Value
                        End If
                    End If

Flag1:
            Next
        Next
    k = k + 5
    Next
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

Aucun commentaire:

Enregistrer un commentaire