vba - 比较源表和目标表，并复制源表中不匹配的数据

Question

我有两张床单 Sht1 和 Sht2。

我正在将 sheet1 的 A 列与 sheet2 的 A 列进行比较。两张表的 A 列都包含 ID。

如果 sheet2 中有不匹配的 ID，那么我想复制 sheet1 中不匹配的行。

我尝试了下面的代码，问题是，它只是多次复制不匹配的 sheet2 最后一行，并且在没有退出的情况下继续运行。

谁能帮助我如何纠正它。

Sub trialtest()
    Dim srcLastRow As Long, destLastRow As Long
    Dim srcWS As Worksheet, destWS As Worksheet
    Dim i As Long, j As Long
    Application.ScreenUpdating = False
    Set srcWS = ThisWorkbook.Sheets("S2")
    Set destWS = ThisWorkbook.Sheets("S1")
    srcLastRow = srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row
    destLastRow = destWS.Cells(destWS.Rows.Count, "A").End(xlUp).Row
    For i = 5 To destLastRow
        For j = 5 To srcLastRow
            If destWS.Cells(i, "A").Value <> srcWS.Cells(j, "A").Value Then
                 destWS.Cells(i, "A") = srcWS.Cells(j, "A")
                 destWS.Cells(i, "B") = srcWS.Cells(j, "B")
                 destWS.Cells(i, "C") = srcWS.Cells(j, "C")
                 destWS.Cells(i, "D") = srcWS.Cells(j, "D")
                 destWS.Cells(i, "E") = srcWS.Cells(j, "E")
                 destWS.Cells(i, "F") = srcWS.Cells(j, "F")
                 destWS.Cells(i, "G") = srcWS.Cells(j, "G")
                 destWS.Cells(i, "H") = srcWS.Cells(j, "H")
                 destWS.Cells(i, "I") = srcWS.Cells(j, "I")
                 destWS.Cells(i, "J") = srcWS.Cells(j, "J")
                 destWS.Cells(i, "K") = srcWS.Cells(j, "K")
                 destWS.Cells(i, "L") = srcWS.Cells(j, "L")
                 destWS.Cells(i, "M") = srcWS.Cells(j, "M")
                 destWS.Cells(i, "N") = srcWS.Cells(j, "N")
                 destWS.Cells(i, "O") = srcWS.Cells(j, "O")
                 destWS.Cells(i, "P") = srcWS.Cells(j, "P")
                 destWS.Cells(i, "Q") = srcWS.Cells(j, "Q")
                 destWS.Cells(i, "R") = srcWS.Cells(j, "R")
                 destWS.Cells(i, "S") = srcWS.Cells(j, "S")
             End If
         Next j
    Next i

    Application.ScreenUpdating = True
End Sub

Answer 1

我知道您已经接受了答案，但是我只想与您分享这种方法:

If i understood your question correctly, if an ID in sheet 1 is not equal to an ID in sheet 2, then replace that sheet 1 ID with the ID from sheet 2?

Option Explicit
Dim i, n As Long

Sub IDReplace()

n = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("Sheet1")
    For i = 2 To n
        If .Cells(i, 1).Value <> .Parent.Sheets("Sheet2").Cells(i, 1).Value Then
            .Cells(i, 1).Value = .Parent.Sheets("Sheet2").Cells(i, 1).Value
        End If
    Next i
End With


End Sub

基于 Sheet 1 是您关注的主要工作表这一事实，您只需计算 Sheet 1 的行数而不是 Sheet2

很高兴能帮助你 :)