excel - 优化了我的 VBA 和错误处理程序返回输出的时间更长

Question

为更快的执行者优化 VBA 代码后，错误处理程序停止生成。请原谅我使用的单词我是 VBA 新手，并且我还缩短了代码的长度以便于阅读。

请参阅下面的代码。

Sub D2dLookup2019()
    
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
      
       
    Set rng = Range("B:B")
    Set rng5 = Range("A:A")
    Set rng2 = Range("G:G")
    Set rng3 = Range("E:E")
    Set rng4 = Range("F:F")
     lookupYear = Array(SValue, "2019")
     lastrow = Sheet11.Range("HS" & Rows.Count).End(xlUp).Row
     MsgBox lastrow
     
     On Error GoTo MyerrorHandler:
     For i = 4 To lastrow
     
     If IsEmpty(Cells(i, 227).Value) = True Then
     Else
             
     Cells(i, 231) = Application.WorksheetFunction.SumIfs(rng3, rng, Cells(i, 227).Value, rng4, Cells(2, 231).Value, rng5, SValue) / Application.WorksheetFunction.SumIfs(rng3, rng, Cells(i, 227).Value, rng5, SValue)
      Cells(i, 231) = Cells(i, 231).Value * Cells(i, 228).Value
      Cells(i, 232) = Application.WorksheetFunction.SumIfs(rng3, rng, Cells(i, 227).Value, rng4, Cells(2, 232).Value, rng5, SValue) / Application.WorksheetFunction.SumIfs(rng3, rng, Cells(i, 227).Value, rng5, SValue)
      Cells(i, 232) = Cells(i, 232).Value * Cells(i, 228).Value
         
    End If

    
MyerrorHandler:
If Err.Number = 6 And Cells(i, 227).Value = "CHOICE ENERGY SERVICES RETAIL" Then
          Cells(i, 231) = 0
          Cells(i, 231) = Cells(i, 231).Value * Cells(i, 228).Value
          Cells(i, 232) = 0
        
         
    
End If

Resume Next
Next i

Exit Sub

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

感谢您的帮助

标签： excelvba