我在兩張紙上有兩個范圍。
我正在嘗試比較這兩個串列的差異，并將任何差異從 Sheet2 復制到 Sheet1。這是我的代碼。我認為它已經接近了，但有些東西是關閉的，因為如果確實洗掉了 Sheet1 上的第 14 行，并且沒有來自 Sheet2 的不同單元格被復制到 Sheet1。這里有什么問題？
Sub Compare()
Dim lastRow1 As Integer
Dim lastRow2 As Integer
Dim foundTrue As Boolean
lastRow1 = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
lastRow2 = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "E").End(xlUp).Row
For i = 2 To lastRow2
foundTrue = False
For j = 2 To lastRow1
If Sheets("Sheet2").Cells(i, 1).Value = Sheets("Sheet1").Cells(j, 1).Value Then
foundTrue = True
Exit For
End If
Next j
If Not foundTrue Then
Sheets("Sheet2").Cells(i).Copy Destination:=Sheets("Sheet1").Rows(lastRow1 - 1)
End If
Next i
Debug.Print i
End Sub
我想結束這個。
uj5u.com熱心網友回復：
沒有什么是除錯會話無法揭示的。
您需要復制到 lastrow 1，而不是 lastrow - 1。
復制第一個值后，您需要以某種方式增加lastRow1. 但是當你在你的（內部）for回圈中使用這個值作為限制時，你不應該修改它。所以我建議你引入一個計數器變數來計算你已經復制了多少行并將其用作偏移量。
而且您還有一些錯誤：
您在 sheet2 中的資料在 E 和 F 列中，但是您比較了“A”列的值（您寫了Sheets("Sheet2").Cells(i, 1).Value）
您的復制命令訪問的來源是.Cells(i). 如果 i 是 10，這將是作業表的第 10 個單元格，即J1- 不是單元格E10。即使它是正確的單元格，您也只會復制一個單元格，而不是兩個。
強制性額外提示：使用Option Explicit（您的變數 i 和 j 未宣告），并且始終使用Long，而不是Integer。
代碼可能看起來像（我重命名foundTrue是因為看到True變數名會傷害我的眼睛）
Dim i As Long, j As Long
For i = 2 To lastRow2
foundValue = False
For j = 2 To lastRow1
If Sheets("Sheet2").Cells(i, 5).Value = Sheets("Sheet1").Cells(j, 1).Value Then
foundValue = True
Exit For
End If
Next j
If Not foundValue Then
addedRows = addedRows 1
Sheets("Sheet2").Cells(i, 5).Resize(1, 2).Copy Destination:=Sheets("Sheet1").Cells(lastRow1, 1).Offset(addedRows)
End If
Next i
但這留下了很大的改進空間。我建議您看看以下內容，在我看來它更干凈，更容易適應。仍有優化空間（例如將資料讀入陣列以加快執行速度），但這是另一回事。
Sub Compare()
Const sourceCol = "E"
Const destCol = "A"
Const colCount = 2
' Set worksheets
Dim sourceWs As Worksheet, destWs As Worksheet
Set sourceWs = ThisWorkbook.Sheets("Sheet2")
Set destWs = ThisWorkbook.Sheets("Sheet1")
' Count rows
Dim lastRowSource As Long, lastRowDest As Long
lastRowSource = sourceWs.Cells(sourceWs.Rows.Count, sourceCol).End(xlUp).Row
lastRowDest = destWs.Cells(destWs.Rows.Count, destCol).End(xlUp).Row
Dim sourceRow As Long, destRow As Long
Dim addedRows As Long
For sourceRow = 2 To lastRowSource
Dim foundValue As Boolean
foundValue = False
For destRow = 2 To lastRowDest
If sourceWs.Cells(sourceRow, sourceCol).Value = destWs.Cells(destRow, destCol).Value Then
foundValue = True
Exit For
End If
Next destRow
If Not foundValue Then
addedRows = addedRows 1
sourceWs.Cells(sourceRow, sourceCol).Resize(1, colCount).Copy Destination:=destWs.Cells(lastRowDest, 1).Offset(addedRows)
End If
Next sourceRow
End Sub
uj5u.com熱心網友回復：
復制差異（回圈）
快速修復
Option Explicit
Sub Compare()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws1 As Worksheet: Set ws1 = wb.Worksheets("Sheet1")
Dim lRow1 As Long: lRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
Dim fRow1 As Long: fRow1 = lRow1
Dim ws2 As Worksheet: Set ws2 = wb.Worksheets("Sheet2")
Dim lRow2 As Long: lRow2 = ws2.Cells(ws2.Rows.Count, "E").End(xlUp).Row
Dim i As Long, j As Long
For i = 2 To lRow2
For j = 2 To lRow1
If ws2.Cells(i, "E").Value = ws1.Cells(j, "A").Value Then Exit For
Next j
' Note this possibility utilizing the behavior of the For...Next loop.
' No boolean necessary.
If j > lRow1 Then ' not found
fRow1 = fRow1 1
ws2.Cells(i, "E").Resize(, 2).Copy ws1.Cells(fRow1, "A")
End If
Next i
MsgBox "Found " & fRow1 - lRow1 & " differences.", vbInformation
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/439938.html