VBA: Find IDNum, tabulate total changes between sheets.

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
77
Office Version
  1. 365
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?

1609281281750.png

1609281313793.png


VBA Code:
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
77
Office Version
  1. 365
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?
 

Forum statistics

Threads
1,144,515
Messages
5,724,811
Members
422,579
Latest member
parsnipsnatcher

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top