VBA: Find IDNum, tabulate total changes between sheets.

[noslenwerd]

Hello,

I wrote the code below to try and see how many times the Name changes between sheets.

I run through each account number in sheet 1, search for that in sheet2, and then compare the "name" Column (b). If it has changed, I add a value of 1 to the 122820 sheet.

Problem is, account number can show up twice on each sheet. I want the formula to only count THE FIRST INSTANCE of the account num that it finds on each sheet. In the example below you will see Acctnum 125 shows as 5 total changes, but with only five dates to look at, it shouldn't be possible to have more than 4 changes. Make sense?

Sub Compare_RollRate()
Dim w1 As Worksheet, w2 As Worksheet, recent As Worksheet
Dim i As Long, sc As Long
Dim c As Range, a As Range, r As Range
sc = Sheets.Count
MsgBox "Sheet count = " & sc
i = 2
Set recent = Sheets(2)  'the most recent sheet that will have roll rate
    Do While sc <> i  'compare all sheets other than dashboard
        MsgBox "i is now equal to " & i
        Set w1 = Sheets(i)
        Set w2 = Sheets(i + 1)
            With w1
              For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
                Set a = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
                Set r = recent.Columns(1).Find(c.Value, LookAt:=xlWhole)
                If Not a Is Nothing Then
                  If .Cells(c.Row, 2).Value <> w2.Cells(a.Row, 2) Then
                    recent.Cells(r.Row, 4).Value = recent.Cells(r.Row, 4).Value + 1
                  End If
                End If
              Next c
            End With
        i = i + 1
    Loop
End Sub

 

[noslenwerd]

EDIT: Also, this actual application of this will be using 13 sheets, each with 13,000 records. Takes about an hour to run. Is there a cleaner way to do this?

Maybe store the 'total changes' in an array?