VBA - как сделать код с циклами For и IF-операторами более эффективным?

Question

Я написал код для сопоставления данных (MaterialPN против MaterialPS и WeekPN против WeekPS) и скопировал соответствующие значения между двумя листами (Packaging Needs - PN и Packaging Staging - PS).

Я уже отключил ScreenUpdating, Calculations and Events. Это увеличило время выполнения с 5 минут до 1 минуты, что по-прежнему довольно медленно (мои данные составляют всего ~3000 строк). Я также попытался принудительно закрыть оператор If, когда WeekPN не равен WeekPS, с использованием GoTo Flag1, но это не заставило мой код работать быстрее.

Есть какие-нибудь советы, как сделать этот код более эффективным?

Заранее благодарю за любую помощь!

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

Answer 1

Несколько предложений - можно было бы быстрее использовать массивы Variant, а также, если бы здесь подходило Match (трудно сказать, не зная, сколько совпадений вы ожидаете сделать - если только одно, то вы также можете Exit For чтобы выйти из цикла, как только вы найдете совпадение)

Sub PackagingNeeds2PackagingStaging()
       
    Const J As Long = 25 'use Const for fixed values
    Dim i As Long, x As Long, l As Long, k As Long, z As Long
    Dim shtPN As Worksheet, shtPS As Worksheet
    Dim MaterialPN, MaterialPS, WeekPS
    
    'use worksheet variables
    Set shtPN = ThisWorkbook.Sheets("Packaging Needs")
    Set shtPS = ThisWorkbook.Sheets("Packaging Staging")
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    i = shtPN.Cells(shtPN.Rows.Count, 5).End(xlUp).Row
    l = shtPS.Cells(shtPS.Rows.Count, 1).End(xlUp).Row
    
    For k = 9 To i Step 5 '<< use Step instead of your line below
        MaterialPN = shtPN.Cells(k, 5).Value '<< moved this up
        For x = 5 To l
            MaterialPS = shtPS.Cells(x, 1).Value  '<< moved this up
            WeekPS = shtPS.Cells(x, 12).Value     '<< ...and this
            For z = 14 To J
                If shtPN.Cells(4, z).Value = WeekPS Then
                    If MaterialPN = MaterialPS Then
                        shtPS.Cells(x, 19).Value = shtPN.Cells(k, z).Value
                    End If
                End If
            Next z
        Next x
        'k = k + 5 '<<< don't change the counter inside a For loop!
    Next k
        
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub