VBA Excel Compare Two Lists for Matches, Output Results on Separate Sheet

Question

Here is the situation:

I am verifying institution data in a database. Each institution has a code associated with it. I want to be sure that the codes in our database are correct and also check that the names in our database contain no errors. I would do this manually, but there are some 1200 records to check.

All my data is contained in a single workbook.

• The first sheet is the information from our database. The institution name is in column B and the code is in column K. The code is stored as a number.
• The second sheet is the up-to-date information. The institution name is in column A and the code is in column D. The code is also stored as a number.
• The third sheet is set up for the results

I want to match codes in the first sheet with codes in the second sheet, then output the results on the third sheet. I want for each code to have a row containing: the institution name from sheet 1, the code, and the institution name from the second sheet. I want to do this so that I can check that the code is valid, that the code in our database corresponds to the correct institution, and finally so I can check the institution name for formatting and other discrepancies.

Here are my problems:

1. Rather than use the original workbook and somehow corrupt the data, I created a copy to test the code on. I wrote a program which works in the test file, but when I copy it over to the original, making any appropriate changes, the code no longer works. Why might this be?
2. Since copying the code seemed to create some problem, I retyped the code, verbatim, into the original file. It worked. But, only sort of. The code ran, but would only execute part of the code contained in the conditional. It would execute the first line in the block, but then skip the rest and then never enter the conditional again. Since everything is indented properly, what could cause this to happen?
3. To try to detect why all of these problems occur, I've tried stepping through the program using F8. It steps through, but never enters the conditional. It just traverses the loops. When using the Step Into (F8) command, does it not evaluate IF statements?
4. To test if my loops are functioning properly, rather than run through ALL the records and have to wait, I will change the FOR loop condition from 1170 to 11. Nothing has changed except the number of times the loop runs. Yet, the program no longer executes. When I change the number back, it works. Why might this be?

Here is my code. It may not be optimal, but I only need to run the comparison ONCE. Even if it took 2 hours, it would be less work than checking them all manually. I know this code works, because I got it to work in the test file. But why doesn't it work in the original and what can I do to make it work?

Sub FindMatches()

    Dim oldRow As Integer
    Dim newRow As Integer
    Dim i As Integer

    i = 2

    For oldRow = 2 To 1170
        For newRow = 2 To 1170
            If Worksheets("Sheet1").Cells(oldRow, 9) = Worksheets("Sheet2").Cells(newRow, 4) Then

                Worksheets("Sheet3").Cells(i, 1) = Worksheets("Sheet1").Cells(oldRow, 2)
                Worksheets("Sheet3").Cells(i, 2) = Worksheets("Sheet1").Cells(oldRow, 9)
                Worksheets("Sheet3").Cells(i, 3) = Worksheets("Sheet2").Cells(newRow, 1)

                i = i + 1

                Exit For
            End If
        Next newRow
    Next oldRow

End Sub

Solution 1

I think you are referencing the wrong column. Column K is 11 not 9

Solution 2

Maybe use Find() instead of the inner loop:

Sub FindMatches()

    Dim shtOld As Worksheet, shtNew As Worksheet, shtMatch As Worksheet
    Dim oldRow As Integer
    Dim newRow As Integer
    Dim i As Integer, id, f As Range

    i = 2

    Set shtOld = ThisWorkbook.Sheets("Sheet1")
    Set shtNew = ThisWorkbook.Sheets("Sheet2")
    Set shtMatch = ThisWorkbook.Sheets("Sheet3")

    For oldRow = 2 To 1170

        id = shtOld.Cells(oldRow, 9)

        Set f = shtNew.Range("D2:D1170").Find(id, , xlValues, xlWhole)
        If Not f Is Nothing Then
            With shtMatch.Rows(i)
                .Cells(1).Value = shtOld.Cells(oldRow, 2)
                .Cells(2).Value = id
                .Cells(3).Value = f.EntireRow.Cells(1)
            End With
            i = i + 1
        End If

    Next oldRow

End Sub

Comments

[user1905080]

Thank you, that was it! I had a column or two hidden which was throwing off my counting. Thank you so much!