How to compare columns for unique data between two workbooks

Question

I want to compare data from column D in fortest1.xlsx with column F in tested.xlsm. If they are matched, then do nothing. If they are not matched, then copy Column A to C in fortest.xlsx to Column C to E in tested.xlsm. May I know how can I achieve this? My code is running without error, however, it doesn't generate any result.

Thanks in advanced.

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)

End Sub enter image description here

Did you step through the code and check what it does? Once you get to a point where you think it should do something and it doesn't, did you check the variables? Which line is the problem? — teylyn
Yes.It seems like never go through the steps for the command below:erow = Range("C" & Rows.Count).End(xlUp).Row + 1 SheetB.Range("A" & j & ":C" & j).Copy Destination:=SheetA.Range("C" & erow & ":E" & erow) — klpw
When you step through, do you realize why not? What is the value of the match variable at that time? When was the variable last set? Do you see where it goes wrong? The variable is still TRUE. Do you see why? — teylyn

teylyn teylyn · Accepted Answer · 2016-02-03T01:46:57

I'm not quite sure I understand what you really want to achieve, but the code has several problems.

Proper indenting is one of the issues. It is not obvious at first glance how the code is nested.

when the comparison is not true, the match variable is not reset to false, so it still shows as True. Fix: move match = False into the j loop, so it is set to False at the beginning of the j loop
The match variable is set and then overwritten seven times in the j loop before it is evaluated and something is done with it.

The suggestions above applied to the code result in this:

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

The code now runs through the i loop twice and in each i loop runs through the j loop seven times. If that is not what you want to achieve, pipe up.

How to compare columns for unique data between two workbooks

1 Answers