如何比较两个工作簿之间唯一数据的列

Question

Sub test()

'application.screenupdating=True Dim WbA As Workbook Set WbA=thisworkbook

Dim WbB As Workbook
Set WbB = Workbooks.Open(Filename:="C:\Users\maggie\Desktop\fortest1.xlsx")

Dim SheetA As Worksheet
Dim SheetB As Worksheet
Set SheetA = WbA.Sheets("up")
Set SheetB = WbB.Sheets("up")

Dim eRowA As Integer
Dim eRowB As Integer
eRowA = SheetA.Range("F" & Rows.Count).End(xlUp).Row 'Last line with data in Workbook A (ActiveWorkbook)
eRowB = SheetB.Range("D" & Rows.Count).End(xlUp).Row  'Last line with data in Workbook B (Opened Workbook)

Dim match As Boolean
Dim erow As Long
Dim i, j As Long
Dim r1, r2 As Range
For i = 1 To eRowA
    Set r1 = SheetA.Range("F" & i)
    match = False
    For j = 1 To eRowB
        Set r2 = SheetB.Range("D" & j)
        If r1 = r2 Then
            match = True
        End If
        Next j
        If Not match Then
        erow = Range("C" & Rows.Count).End(xlUp).Row + 1
        SheetB.Range("A" & j & ":C" & j).Copy Destination:=SheetA.Range("C" & erow & ":E" & erow)
End If
Next i
WbB.Close (False)

在此输入图像描述

Answer 1

我不太确定我是否理解您真正想要实现的目标，但是代码有几个问题。

正确的缩进是问题之一。乍一看并不明显代码是如何嵌套的。

• 当比较不为true时，match变量不会重置为false，因此它仍然显示为true。fix：将match=False移动到j循环中，因此在j循环开始时将其设置为False
• 设置match变量，然后在j循环中重写七次，然后对其进行计算，并对其执行某些操作。

For i = 1 To eRowA
    Set r1 = SheetA.Range("F" & i)
'    match = False ' move this into the j loop
    For j = 1 To eRowB
        match = False
        Set r2 = SheetB.Range("D" & j)
        If r1 = r2 Then
            match = True
        End If
'        Next j   ' this loops too early and overwrites the match variable
        If Not match Then
            erow = Range("C" & Rows.Count).End(xlUp).Row + 1
            SheetB.Range("A" & j & ":C" & j).Copy Destination:=SheetA.Range("C" & erow & ":E" & erow)
        End If
    Next j
Next i