Watch out for your unqualified ranges:

With ws1
Set rngA = .Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
End With

With ws2
Set rngB = .Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
End With

becomes:

With ws1
Set rngA = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

With ws2
Set rngB = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With

(I added dots in front of each Cells() and in front of Rows.count (rows.count
would have worked, though--since there are the same number of rows on each
worksheet.))


Toppers wrote:

John,
Here is a "general" routine which compares two columns (lists)
of data - MASTER in column A and UPDATE in column B. Data starts in row 2.

It lists Matches, Master Only and Update only. You can adapt for your own
needs.

HTH

Sub CompareTwoColumns()

Dim rngA As Range
Dim rngB As Range
Dim rw1 As Long
Dim rw2 As Long
Dim rw3 As Long

Dim Start as Double, Finish as Double, TotalTime as Double

Start = Timer ' Set start time.
headings = Array("Master", "Update", "Matched", "Master Only", "Update Only")

Application.ScreenUpdating = False

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet1")

With ws1
Set rngA = .Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
End With

With ws2
Set rngB = .Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
End With

rw1 = 2
rw2 = 2
rw3 = 2

ws1.Columns("C:E").ClearContents
ws1.Range("a1").Resize(1, 5) = headings

' In column A and column B

For Each Cell In rngA
If Not IsError(Application.Match(Cell.Value, rngB, 0)) Then
ws1.Cells(rw1, 3).Value = Cell.Value
rw1 = rw1 + 1
End If
Next

' In column A but not in column B

For Each Cell In rngA
If IsError(Application.Match(Cell.Value, rngB, 0)) Then
ws1.Cells(rw2, 4).Value = Cell.Value
rw2 = rw2 + 1
End If
Next

' In column B but not in column A

For Each Cell In rngB
If IsError(Application.Match(Cell.Value, rngA, 0)) Then
ws1.Cells(rw3, 5).Value = Cell.Value
rw3 = rw3 + 1
End If
Next

Application.ScreenUpdating = True

Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Ran for " & TotalTime & " seconds"

End Sub

"john.jacobs71" wrote:

James,

Thank you. How can I view the sub routine?

"john.jacobs71" wrote:

I am trying to find matches in two very long columns of digit strings. I
want to create a loop to compare the first digit string in column A to each
digit string in column B and return any matches and then to continue to the
next digit in column A (look for matches in B) and so forth...

--

Dave Peterson