时间戳

首页 > 解决方案 > Excel宏需要很长时间

excel - Excel宏需要很长时间

问题描述

我正在使用下面的excel，它需要很长时间才能完成。

通常我会在发票表和 GRN 表中有 30k 条记录。

谁能建议我以更快的方式完成这项任务？

Sub CheckReturn()
Dim LastInvRow As Long
Dim LastGRNRow As Long
Dim i As Integer
Dim j As Integer

Application.Calculation = xlManual

LastInvRow = Sheets("Invoice").Cells(Rows.Count, "A").End(xlUp).Row
LastGRNRow = Sheets("GRN").Cells(Rows.Count, "A").End(xlUp).Row
For i = 4 To LastInvRow
  For j = 2 To LastGRNRow
    If Sheets("GRN").Cells(j, 11).Value = "Customer Return" And _
       Sheets("GRN").Cells(j, 3).Value = Sheets("Invoice").Cells(i, 7).Value And _
       Sheets("GRN").Cells(j, 18).Value = Sheets("Invoice").Cells(i, 19).Value Then
      
       Sheets("GRN").Cells(j, 34).Copy
       Sheets("Invoice").Cells(i, 48).PasteSpecial Paste:=xlPasteValues
       Sheets("GRN").Cells(j, 35).Copy
       Sheets("Invoice").Cells(i, 49).PasteSpecial Paste:=xlPasteValues
       Sheets("GRN").Cells(j, 36).Copy
       Sheets("Invoice").Cells(i, 50).PasteSpecial Paste:=xlPasteValues
      Exit For
    End If
  Next j
Next i

Application.Calculation = xlAutomatic

MsgBox "Completed - " & Now()
End Sub

标签： excelvba

解决方案

这个应该快一点。因为每次 for j 循环开始时，您都不会得到 a 和 b 的值。

Sub CheckReturn()
Dim LastInvRow As Long
Dim LastGRNRow As Long
Dim i As Integer
Dim j As Integer

Application.Calculation = xlManual

LastInvRow = Sheets("Invoice").Cells(Rows.Count, "A").End(xlUp).Row
LastGRNRow = Sheets("GRN").Cells(Rows.Count, "A").End(xlUp).Row
For i = 4 To LastInvRow
  a=Sheets("Invoice").Cells(i, 7).Value
  b=Sheets("Invoice").Cells(i, 19).Value
  c=Sheets("GRN").Cells(j, 34).Value
  d=Sheets("GRN").Cells(j, 35).Value
  e=Sheets("GRN").Cells(j, 36).Value
  For j = 2 To LastGRNRow
    If Sheets("GRN").Cells(j, 11).Value = "Customer Return" And _
       Sheets("GRN").Cells(j, 3).Value = a And _
       Sheets("GRN").Cells(j, 18).Value = b Then
      
       Sheets("Invoice").Cells(i, 48).Value=c
       Sheets("Invoice").Cells(i, 49).Value=d
       Sheets("Invoice").Cells(i, 50).Value=e
       Application.Statusbar= i*j*100/LastInvRow*LastGRNRow & "%"
      Exit For
    End If
  Next j
Next i

Application.Calculation = xlAutomatic

MsgBox "Completed - " & Now()
End Sub

状态栏将以百分比显示您的任务进度。

推荐阅读