excel - 代码比较两个列表重复数据并复制到另一个列表

Question

我会帮助我了解我的代码，它从 sheet1 两列 a、b 进行比较，并将重复转移到 sheet2 列 c

     Sub COPY1()
Dim i
Dim LastRow As Long
LastRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
    If Sheets("sheet1").Cells(i, "A").Value = Sheets("sheet1").Cells(i, "B").Value Then
  Count = Application.WorksheetFunction.CountIf(Range("B1:B" & i), Sheets("sheet1").Cells(i, "A"))
        If Count > 1 Then
        Sheets("sheet1").Cells(i, "A").COPY Destination:=Sheets("sheet2").Range("B" & 
    Rows.Count).End(xlUp).Offset(1)
            End If
End If
Next i
End Sub

标签： excelvba

Answer 1

试试这个：

Sub COPY1()
    Dim i As Long
    Dim LastRow As Long
    With Sheets("sheet1")
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row

        For i = 2 To LastRow
            v = .Cells(i, "A").Value
                For j = 2 To LastRow
                    If v = .Cells(j, "B").Value Then
                        .Cells(i, "A").Copy Destination:=Sheets("sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
                    End If
                Next j
        Next i
    End With
End Sub