我有两张Shet Sht1和Sht2 .
我将sheet1的A列与sheet2的A列进行比较 . 两个表格的A列包含ID .
如果sheet2中存在不匹配的ID,那么我想复制sheet1中不匹配的行 .
我尝试了下面的代码,问题是,它只是多次复制sheet2的不匹配的最后一行并且在没有Exit的情况下继续运行 .
任何人都可以帮助我如何纠正它 .
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
3 回答
试试这个代码
如果有什么不清楚,请告诉我 .
我知道你已经接受了答案,我只想和你分享这个方法:
基于Sheet 1是您关注的主要工作表这一事实,您只需要计算Sheet 1而不是Sheet2的行数
乐于帮助 :)
我会在这里使用find方法 . 使用find方法,您可以查看Sheet S2中的ID是否在Sheet S1中 .
如果它在Sheet S1中找到ID,则变量 c 具有ID值 . 如果它不是来自Sheet S1的't find the ID in Sheet S1, the value of c is Nothing. Then the code will copy the Row at the End your List of ID' .