请求开发VBA脚本：匹配引用替换Sheet2整行，无匹配则追加至末尾

Got it, let's build a straightforward VBA script that handles your requirement perfectly. The script will loop through all rows in Sheet1, check if each entry's reference exists in Sheet2, replace the entire matching row if found, or append the new entry to the end of Sheet2 if not.

Key Notes Before You Start #

• I've assumed the reference column (the column you use to match entries) is column A (column index 1). If your reference is in a different column, just change the matchColumn variable value in the code (e.g., 2 for column B).
• The script skips the header row (assuming both sheets have headers in row 1). If your data starts at row 1 with no header, adjust the starting row in the loop.

Full VBA Code #

Sub SyncSheet1ToSheet2()
    Dim wsNew As Worksheet, wsHist As Worksheet
    Dim lastRowNew As Long, lastRowHist As Long
    Dim matchColumn As Integer
    Dim searchRange As Range, foundCell As Range
    Dim i As Long
    
    ' Set worksheet references (change names if your sheets have different names)
    Set wsNew = ThisWorkbook.Sheets("Sheet1")
    Set wsHist = ThisWorkbook.Sheets("Sheet2")
    
    ' Set the column index for matching (1 = Column A, adjust as needed)
    matchColumn = 1
    
    ' Get last row with data in each sheet
    lastRowNew = wsNew.Cells(wsNew.Rows.Count, matchColumn).End(xlUp).Row
    lastRowHist = wsHist.Cells(wsHist.Rows.Count, matchColumn).End(xlUp).Row
    
    ' Set the search range in Sheet2's match column
    Set searchRange = wsHist.Range(wsHist.Cells(1, matchColumn), wsHist.Cells(lastRowHist, matchColumn))
    
    ' Loop through each row in Sheet1 (start at row 2 to skip header)
    For i = 2 To lastRowNew
        ' Get the reference value from current row in Sheet1
        Dim refValue As Variant
        refValue = wsNew.Cells(i, matchColumn).Value
        
        ' Skip empty rows in Sheet1
        If refValue <> "" Then
            ' Search for the reference in Sheet2's match column
            Set foundCell = searchRange.Find(What:=refValue, LookIn:=xlValues, LookAt:=xlWhole)
            
            If Not foundCell Is Nothing Then
                ' Match found: replace entire row in Sheet2 with Sheet1's row
                wsNew.Rows(i).Copy
                wsHist.Rows(foundCell.Row).PasteSpecial Paste:=xlPasteAll
                Application.CutCopyMode = False
            Else
                ' No match found: append Sheet1's row to end of Sheet2
                lastRowHist = lastRowHist + 1
                wsNew.Rows(i).Copy wsHist.Rows(lastRowHist)
                ' Update search range to include the new row
                Set searchRange = wsHist.Range(wsHist.Cells(1, matchColumn), wsHist.Cells(lastRowHist, matchColumn))
            End If
        End If
    Next i
    
    ' Optional: Notify user when done
    MsgBox "Data sync completed successfully!", vbInformation
End Sub

How It Works #

1. Worksheet Setup: We first define references to Sheet1 (new data) and Sheet2 (historical data).
2. Match Column: The matchColumn variable lets you specify which column to use for matching entries (adjust this to your actual reference column).
3. Last Row Calculation: We find the last row with data in both sheets to avoid looping through empty rows.
4. Loop Through Sheet1: For each row in Sheet1 (skipping the header), we:
   • Grab the reference value.
   • Use Find to check if this reference exists in Sheet2's match column.
   • If found: Copy the entire row from Sheet1 and paste it over the matching row in Sheet2.
   • If not found: Append the row to the end of Sheet2 and update our search range to include the new entry.
5. Cleanup: We clear the copy/paste mode and show a completion message (you can remove this if you don't need it).

Tips for Usage #

• Always test the script on a copy of your workbook first to avoid accidental data loss.
• If your reference values are case-sensitive, add MatchCase:=True to the Find method.
• If you have large datasets, adding Application.ScreenUpdating = False at the start and Application.ScreenUpdating = True at the end will speed up the script.

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