顯示的代碼是一個子例程，它比較兩個陣列中的資料，查找出現在一個串列中但不在另一個串列中的帳號。如果它發現缺少的，它會將帳戶資訊添加到作業表中。在平常的一天，它會在 2,500 - 4,000 條記錄中找到 10-20 個缺失的帳戶，具體取決于我們在這個月中的距離。處理 i 回圈需要 15-20 分鐘。與作業表的互動很少。我不知道如何改進代碼以使其運行得更快。建議表示贊賞。
For i = 1 To TempCount
If i Mod 5 = 0 Then DoEvents
FoundMatch = 0
percent = i / TempCount * 100
Application.StatusBar = "Checking for missing accounts. Processing row " & i & " of " & TempCount & " - " & percent & "%"
For y = 1 To OppListRow
If Saved_User_Input(i, 3) = AccountList(y, 3) Then
FoundMatch = 1
End If
Next
If FoundMatch = 0 Then
n = n 1
Opportunities.Cells(n, 2) = Saved_User_Input(i, 1)
Opportunities.Cells(n, 3) = Saved_User_Input(i, 2)
Opportunities.Cells(n, 4) = Saved_User_Input(i, 3)
Opportunities.Cells(n, 5) = Saved_User_Input(i, 4)
Opportunities.Cells(n, 6) = Saved_User_Input(i, 5)
Opportunities.Cells(n, 7) = Saved_User_Input(i, 6)
Opportunities.Cells(n, 8) = Saved_User_Input(i, 7)
Opportunities.Cells(n, 9) = Saved_User_Input(i, 8)
Opportunities.Cells(n, 10) = Saved_User_Input(i, 9)
Opportunities.Cells(n, 11) = Saved_User_Input(i, 10)
Opportunities.Cells(n, 12) = Saved_User_Input(i, 11)
Opportunities.Cells(n, 13) = Saved_User_Input(i, 12)
Opportunities.Cells(n, 14) = Saved_User_Input(i, 13)
Opportunities.Cells(n, 15) = Saved_User_Input(i, 14)
Opportunities.Cells(n, 16) = Saved_User_Input(i, 15)
End If
Next
uj5u.com熱心網友回復：
用這一個替換所有Opportunities.Cells行
Opportunities.Cells(n, 2).Resize(1, 15).Value2 = WorksheetFunction.Index(Saved_User_Input, i, 0)
編輯（消除一個回圈）
替換下面的回圈
For y = 1 To OppListRow
If Saved_User_Input(i, 3) = AccountList(y, 3) Then
FoundMatch = 1
End If
Next
與下面的行
On Error Resume Next
foundMatch = WorksheetFunction.Match(Saved_User_Input(i, 3), WorksheetFunction.Index(AccountList, 0, 3), 0)
foundMatch = Abs(CBool(Err.Number = 0))
On Error GoTo 0
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/471472.html