Slow, nested For loop-CodePudding

Code shown is a subroutine that compares the data in two arrays looking for account numbers that appear in one list but not the other. If it finds one that's missing, it adds the account information to a Sheet. On a usual day, it finds 10-20 missing accounts out of 2,500 - 4,000 records depending on how far through the month we are. It's been taking 15-20 minutes for it process the i loop. Interaction with the Sheet is minimal. I can't figure out how to improve the code to make it run more quickly. Suggestions appreciated.

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

CodePudding user response：

Replace all your Opportunities.Cells lines with this single one

Opportunities.Cells(n, 2).Resize(1, 15).Value2 = WorksheetFunction.Index(Saved_User_Input, i, 0)

Edit (to eliminate one loop)

Replace the loop below

For y = 1 To OppListRow
    If Saved_User_Input(i, 3) = AccountList(y, 3) Then
        FoundMatch = 1
    End If
Next

with the lines below

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