跨工作表IF+VLOOKUP代码循环效率低，求优化方案

Hey there! Let's fix that slow-running VBA code you've got. First, let's break down why the original version is lagging:

• You’re repeatedly setting the entire G-column formula inside the loop every time a row meets the D>200 condition — this is a massive waste of resources, as it overwrites the same range dozens or hundreds of times unnecessarily.
• Cell-by-cell operations in loops are inherently slow in VBA; shifting to memory-based arrays and fast lookup tools will make a huge difference.

Here’s a revised, much faster version of your code:

Private Sub CommandButton1_Click()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim dataWs1 As Variant, dataWs2 As Variant
    Dim resultArr As Variant
    Dim lookupDict As Object
    Dim i As Long
    
    ' Turn off Excel features that slow down code execution
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Set lookupDict = CreateObject("Scripting.Dictionary")
    
    ' Get last rows for both sheets
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    ' Load all data into arrays (memory operations are way faster than cell operations)
    dataWs1 = ws1.Range("A4:G" & lastRow1).Value
    dataWs2 = ws2.Range("A2:B" & lastRow2).Value
    
    ' Populate dictionary with Sheet2 data for instant lookups
    For i = LBound(dataWs2, 1) To UBound(dataWs2, 1)
        ' Use column A as the key, column B as the value (skip duplicates if they exist)
        If Not lookupDict.Exists(dataWs2(i, 1)) Then
            lookupDict.Add dataWs2(i, 1), dataWs2(i, 2)
        End If
    Next i
    
    ' Process each row in Sheet1's data array
    ReDim resultArr(1 To UBound(dataWs1, 1), 1 To 1) ' Array to store G-column results
    For i = LBound(dataWs1, 1) To UBound(dataWs1, 1)
        If dataWs1(i, 4) > 200 Then ' Check column D (4th column in the array)
            ' Look up the value in our dictionary
            If lookupDict.Exists(dataWs1(i, 1)) Then
                resultArr(i, 1) = lookupDict(dataWs1(i, 1))
            Else
                resultArr(i, 1) = "" ' Match your original empty text behavior
            End If
        Else
            resultArr(i, 1) = "Not found"
        End If
    Next i
    
    ' Write the entire result set back to Sheet1 in one go
    ws1.Range("G4:G" & lastRow1).Value = resultArr
    
    ' Restore Excel's default settings
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    ' Clean up objects
    Set lookupDict = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

Key Improvements Explained: #

• Disabled Background Excel Features: Turning off screen updates, events, and switching to manual calculation prevents Excel from doing unnecessary work while your code runs.
• Array-Based Processing: We load all relevant data into memory arrays first, so we’re not reading/writing to cells one by one — this is the biggest speed boost for large datasets.
• Dictionary Lookup: Instead of using VLOOKUP (which scans the entire range every time), we use a Scripting.Dictionary to store Sheet2’s lookup values. Dictionary lookups are nearly instant, even with thousands of rows.
• Single Write Operation: After processing all rows in the array, we write the entire result set back to the worksheet in one step, instead of updating cells individually.

Bonus: Formula-Only Alternative (No VBA) #

If you’d rather skip VBA entirely, use this formula in cell G4 and drag it down to cover your rows:

=IF(D4>200, IFERROR(VLOOKUP(A4, Sheet2!$A$2:$B$1000, 2, FALSE), ""), "Not found")

(Adjust Sheet2!$A$2:$B$1000 to match your actual last row in Sheet2, or use a dynamic range like Sheet2!$A:$B if you prefer.)

This formula approach is simpler and avoids VBA, though for very large datasets (10k+ rows), the VBA dictionary method will still be faster.

内容的提问来源于stack exchange，提问作者alex2002