跨工作簿数据合并至单列时行匹配错误的VBA代码问题咨询

Let's break down why your code is writing values to the wrong rows, and fix it up properly.

The Core Issues in Your Current Code #

Your main problem comes from relying on Activate and not explicitly referencing workbooks/worksheets—this leads to unexpected context switches where your code is accidentally reading/writing from the wrong sheet. Here's the breakdown:

• You're using Rows.Count without tying it to a specific worksheet, so when you activate the source workbook mid-loop, the loop's row range switches to the source sheet's row count instead of the destination's.
• Cells(i, sourceCol) isn't qualified with a worksheet object, so after activating the source workbook, this starts pointing to cells in the source sheet instead of the destination sheet you intended.
• Using Selection.Find is risky because Selection depends on whatever the user had selected when running the macro—you should explicitly define the range to search in the source workbook.

Corrected VBA Code #

Here's the fixed version with explicit object references and better practices:

Sub CombineWorkbooks()
    Dim wbSource As Workbook
    Dim wsSource As Worksheet ' Explicit worksheet reference
    Dim wbDest As Workbook
    Dim wsDest As Worksheet ' Explicit worksheet reference
    Dim i As Long
    Dim searchVal As Variant
    Dim matchCell As Range
    Dim sourceVal1 As Variant
    Dim sourceVal2 As Variant
    
    ' Set workbook references (update sheet names to match your actual sheets!)
    Set wbSource = Workbooks.Open(Filename:="CopyFromWorkbookSource.xlsx", UpdateLinks:=3)
    Set wsSource = wbSource.Worksheets("Sheet1") ' Replace with your source sheet name
    Set wbDest = Workbooks.Open(Filename:="CopyFromWorkbookDest.xlsm", UpdateLinks:=3)
    Set wsDest = wbDest.Worksheets("Sheet1") ' Replace with your destination sheet name
    
    ' Define columns (adjust these numbers to match your needs)
    Const DEST_SEARCH_COL As Integer = 1 ' Column in destination to search for "*WI*"
    Const SOURCE_SEARCH_COL As Integer = 1 ' Column in source to match the value
    Const SOURCE_VAL1_COL As Integer = 2 ' Column in source to get first value
    Const SOURCE_VAL2_COL As Integer = 3 ' Column in source to get second value
    Const DEST_WRITE_COL As Integer = 2 ' Column in destination to write merged value
    
    ' Loop only through used rows in destination sheet (more efficient than Rows.Count)
    For i = 1 To wsDest.Cells(wsDest.Rows.Count, DEST_SEARCH_COL).End(xlUp).Row
        searchVal = wsDest.Cells(i, DEST_SEARCH_COL).Value
        
        ' Check if cell contains "WI" and isn't empty
        If Not IsEmpty(searchVal) And searchVal Like "*WI*" Then
            ' Search for the value in the source sheet's specific column
            Set matchCell = wsSource.Columns(SOURCE_SEARCH_COL).Find( _
                What:=searchVal, _
                LookIn:=xlValues, _
                LookAt:=xlWhole ' Use xlPart if you need partial matches, adjust as needed
            )
            
            If Not matchCell Is Nothing Then
                ' Get values from the matching row in source
                sourceVal1 = wsSource.Cells(matchCell.Row, SOURCE_VAL1_COL).Value
                sourceVal2 = wsSource.Cells(matchCell.Row, SOURCE_VAL2_COL).Value
                
                ' Write merged value to the correct row in destination
                wsDest.Cells(i, DEST_WRITE_COL).Value = sourceVal1 & Chr(10) & sourceVal2
                ' Optional: Enable wrap text for the destination cell
                wsDest.Cells(i, DEST_WRITE_COL).WrapText = True
            End If
        End If
    Next i
    
    ' Optional: Save and close workbooks if needed
    ' wbDest.Save
    ' wbSource.Close SaveChanges:=False
End Sub

Key Improvements Explained #

1. Explicit Worksheet References: We use wsSource and wsDest to directly reference the specific sheets in each workbook—no more relying on Activate, which eliminates context confusion.
2. Targeted Row Loop: Instead of looping every row in the sheet (Rows.Count), we find the last used row in the destination's search column with .End(xlUp)—this makes the macro run much faster and avoids unnecessary checks on empty rows.
3. Defined Search Range: We search only in the source sheet's specific column (wsSource.Columns(SOURCE_SEARCH_COL)), so the Find method knows exactly where to look, instead of using the unpredictable Selection.
4. Clear Column Constants: Using Const for column numbers makes the code easier to adjust later—just change the numbers once if your column layout changes.
5. Safe Match Handling: We check If Not matchCell Is Nothing before trying to access its row, preventing runtime errors if no match is found.

How to Adapt This to Your Workbooks #

• Replace "Sheet1" with the actual names of your source and destination worksheets.
• Adjust the Const values for columns to match your specific layout (e.g., if your destination's search column is column C instead of A, set DEST_SEARCH_COL = 3).
• If you need partial matches in the source (instead of exact matches), change LookAt:=xlWhole to LookAt:=xlPart.

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