Excel VBA中IF/AND语句问题：跨工作表匹配列值后复制数据

Hey there! Let's tackle this problem step by step since you're new to writing VBA code—totally get the frustration with that first If statement tripping you up. Let's start with fixing the core logic, then break down where things might have gone wrong.

Since your rows are out of order between the two sheets, we need to check every row in Ref against each row in Status (or use a faster method for larger datasets). Here are two options:

Option 1: Basic Nested Loops (Easy to Understand) #

This is straightforward for beginners—we loop through each row in Status, then scan Ref to find a match for both conditions.

Sub MatchAndCopyBasic()
    Dim wsStatus As Worksheet
    Dim wsRef As Worksheet
    Dim lastRowStatus As Long
    Dim lastRowRef As Long
    Dim i As Long ' Row counter for Status
    Dim j As Long ' Row counter for Ref
    
    ' Set references to your worksheets (avoids messy Select/Activate)
    Set wsStatus = ThisWorkbook.Worksheets("Status")
    Set wsRef = ThisWorkbook.Worksheets("Ref")
    
    ' Find the last row with data in key columns
    lastRowStatus = wsStatus.Cells(wsStatus.Rows.Count, "F").End(xlUp).Row
    lastRowRef = wsRef.Cells(wsRef.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each row in Status (skip header row if row 1 is headers)
    For i = 2 To lastRowStatus
        ' Loop through Ref to find a matching row
        For j = 2 To lastRowRef
            ' Check both conditions: F matches B AND Q matches C
            If wsStatus.Cells(i, "F").Value = wsRef.Cells(j, "B").Value And _
               wsStatus.Cells(i, "Q").Value = wsRef.Cells(j, "C").Value Then
                ' Copy Ref!F to Status!H once a match is found
                wsStatus.Cells(i, "H").Value = wsRef.Cells(j, "F").Value
                Exit For ' No need to check other Ref rows once we have a match
            End If
        Next j
    Next i
    
    MsgBox "Matching done! Check Status column H.", vbInformation
End Sub

Option 2: Dictionary Method (Faster for Large Datasets) #

If you have 1500+ rows, nested loops can get slow. Using a dictionary lets us store Ref data once, then look up matches in one pass through Status.

Sub MatchWithDictionary()
    Dim wsStatus As Worksheet
    Dim wsRef As Worksheet
    Dim lastRowStatus As Long
    Dim lastRowRef As Long
    Dim i As Long
    Dim refDict As Object ' Scripting.Dictionary
    
    Set wsStatus = ThisWorkbook.Worksheets("Status")
    Set wsRef = ThisWorkbook.Worksheets("Ref")
    Set refDict = CreateObject("Scripting.Dictionary")
    
    ' Populate the dictionary with a unique key (B + C) and corresponding F value
    lastRowRef = wsRef.Cells(wsRef.Rows.Count, "B").End(xlUp).Row
    For i = 2 To lastRowRef
        ' Create a unique key by combining B and C values (use a separator like | to avoid collisions)
        Dim key As String
        key = wsRef.Cells(i, "B").Value & "|" & wsRef.Cells(i, "C").Value
        ' Store the F value in the dictionary
        refDict(key) = wsRef.Cells(i, "F").Value
    Next i
    
    ' Now loop through Status and match using the dictionary
    lastRowStatus = wsStatus.Cells(wsStatus.Rows.Count, "F").End(xlUp).Row
    For i = 2 To lastRowStatus
        Dim statusKey As String
        statusKey = wsStatus.Cells(i, "F").Value & "|" & wsStatus.Cells(i, "Q").Value
        ' Check if the key exists in our dictionary
        If refDict.Exists(statusKey) Then
            wsStatus.Cells(i, "H").Value = refDict(statusKey)
        End If
    Next i
    
    MsgBox "Fast matching complete!", vbInformation
End Sub

Here are the most common mistakes new VBA writers make with this kind of condition:

• Missing the And operator: If you wrote something like If (Condition1) (Condition2) instead of If Condition1 And Condition2, VBA throws a syntax error immediately.
• Unqualified cell references: Forgetting to specify the worksheet (e.g., writing Cells(i, "F") instead of wsStatus.Cells(i, "F")) means VBA uses the currently active sheet, which might not be the one you want.
• Type mismatch: If your columns have mixed data types (e.g., Status!F is text "123" and Ref!B is number 123), they won't match. Fix this by converting both to strings: CStr(wsStatus.Cells(i, "F").Value) = CStr(wsRef.Cells(j, "B").Value)
• Bad line continuation: If your condition spans two lines without the underscore _ at the end of the first line, VBA treats the second line as a new statement, breaking the If logic.
• Empty cells: If some rows have blank values in F/Q/B/C, they might accidentally match other blank rows. Add a check like If Not IsEmpty(wsStatus.Cells(i, "F")) And Not IsEmpty(wsStatus.Cells(i, "Q")) Then before the main condition.

• Set breakpoints: Click the gray margin next to your If statement to pause the code there. Hover over cell references (like wsStatus.Cells(i, "F")) to see exactly what value is being compared.
• Print to the Immediate Window: Add Debug.Print wsStatus.Cells(i, "F").Value, wsRef.Cells(j, "B").Value inside the loop to see values in real time. Press Ctrl+G to open this window.
• Test with small data: Copy a few rows from both sheets into a test workbook to debug without waiting for 1500 rows to process.

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