Searching for duplicates

[Nick Vittum]

Did you ever get to the line

Code: Select all

        Set c = Range("C" & r1 + 1 & ":C" & Rows.Count).Find(What:=v, LookAt:=xlWhole)

or did the code exit before that?

Yes, we got to that line, 11 times through the cycle. I failed to note previously that xlWhole remains equal to 1, throughout. I'm guessing that's why r2 never changes?

We did not ever get to the line

Code: Select all

   r2 = c.Row

[HansV]

In the loop, the variable v is set to the value of the cell in column C and row r1 (the latter varies from 1 to 11 in the loop).
The line

Code: Select all

        Set c = Range("C" & r1 + 1 & ":C" & Rows.Count).Find(What:=v, LookAt:=xlWhole)

then tries to find this value in column C from the row below (r1 + 1) to the very last row (Rows.Count).
On my PC, that line DOES find it; apparently it doesn't on your PC. And that is weird, since we can see that there are duplicates.

Does this version work for you?

Code: Select all

Private Sub cmdDuplicates_Click()
    Static r1 As Long
    Dim v As Variant
    Dim c As Range
    Dim r2 As Long
    Dim m As Long
    Dim a As Variant
    m = Range("C" & Rows.Count).End(xlUp).Row
    a = Range("C1:C" & m).Value
    Do
        Do
            r1 = r1 + 1
            If r1 >= m Then
                MsgBox "End of data", vbInformation
                Exit Sub
            End If
            v = a(r1, 1)
        Loop Until v <> ""
        For r2 = r1 + 1 To m
            If a(r2, 1) = a(r1, 1) Then
                Range("D" & r1).Value = "Duplicate"
                Range("D" & r2).Value = "Duplicate"
                Me.TextBox1 = Range("A" & r1).Value
                Me.TextBox2 = Range("B" & r1).Value
                Me.TextBox3 = Range("C" & r1).Value
                Me.TextBox4 = Range("A" & r2).Value
                Me.TextBox5 = Range("B" & r2).Value
                Me.TextBox6 = Range("C" & r2).Value
                Exit Sub
            End If
        Next r2
    Loop
End Sub

[Nick Vittum]

Remember, I am still running Win7. It doesn't seem like that should matter, but who knows?

I'll try the new code. And again, thanks so much for your time.

[Nick Vittum]

That does it, by George!! (Or, no, "by Hans.")

Hans, you're a prince!

[snb]

Just to illustrate another method.

[Nick Vittum]

Just to illustrate another method.

I love it. I only wish I understood what's going on with the code.

But as before, it trips over blank cells. Even if I delete the empty rows, it shows empty textboxes in the lower row when there are no matches (which seems to me part and parcel of the same issue, if only I understood it). Or is this just my system? After my experience with Hans's code, I'm growing quite suspicious of my system...

[snb]

If the second boxes are left blank it means the row has no duplicate. Simple, eh ?
You can step through tne code using F8.
If you want to skip empty rows you can adapt the code yourself.

[Nick Vittum]

If you want to skip empty rows you can adapt the code yourself.

Thank you. I will try to understand it. It will be a good learning experience

[snb]

That's what this is all about.

[Nick Vittum]

So I incorporated this (with a few minor changes) into my accounting workbook yesterday, and put it to work today—and immediately discovered a problem I hadn't seen until "field testing." It's virtually the same as the problem that I had with the "Next Blank" code, which Hans solved for me:

That is to say, it "remembers" where it left off. So if, for instance, if I run through the sheet, glance at the duplicates it finds, then go back to fix one I know is not valid, and then try to run through the sheet to double-check, it just says "No more duplicates." Because that's where it left off.

That's bad enough; I don't trust either myself or my computer and so I double-check things as a matter of practice, and it won't let me do it. But to make matters worse, if I go on to another sheet (where I know there's a duplicate, because I put it there) it still remembers "No more duplicates" and won't find the one on the new sheet. Bear in mind the form has been unloaded in between, because it automatically unloads when I say "Okay" to the "No more duplicates" message.

Here is the code as it stands now. The very first line after the declarations is comparable to the one you altered, Hans, to fix the problem with the "Next Blank" code—but I can't see the parallel between them well enough to judge how to change it.

Code: Select all

Sub Find_Duplicates()
    Static r1 As Long, r2 As Long, m As Long
    Dim v As Variant, a As Variant
    Dim UFD As Object
    Set UFD = UF_Dupes
  
    m = Range("D" & Rows.Count).End(xlUp).Row
    a = Range("D1:D" & m).Value
    Do
        Do
            r1 = r1 + 1
            If r1 >= m Then
                UFD.Hide
                MsgBox "No more duplicates", vbInformation, "The End"
                GoTo TheEnd                         ' added 04/30/20~5.07PM
            End If
            v = a(r1, 1)
        Loop Until v <> ""
        For r2 = r1 + 1 To m
            If a(r2, 1) = a(r1, 1) Then
                UFD.TextBox1 = Range("A" & r1).Value
                UFD.TextBox2 = Range("C" & r1).Value
                UFD.TextBox3 = Range("D" & r1).Value
                UFD.TextBox4 = Range("A" & r2).Value
                UFD.TextBox5 = Range("C" & r2).Value
                UFD.TextBox6 = Range("D" & r2).Value
                UFD.TextBox7 = r1
                UFD.TextBox8 = r2
                Exit Sub
            End If
        Next r2
    Loop
TheEnd:     Unload UFD
    
End Sub

Thank you...

[HansV]

It looks like Find_Duplicates is in a standard module. Does this work better?

Dupes.xlsm

If not, please attach a new sample workbook.

[Nick Vittum]

It does indeed. Thank you!

So now, it appears to me that nothing has changed except that the code is now placed directly behind the button instead of in a standard module (and the no-longer-necessary UFD variable is eliminated). Is that accurate?
    Is the reason it works there and not where I had it because the standard module was not affected by the form being unloaded, but if it's within the form's code, the "memory" is erased when the form is unloaded?

One of the reasons I had it in a separate module (and why I rearranged the code slightly) was so I could also call the routine from the form's activate event. That way, when the Dupes form is called (from a button on the primary userform), if there are duplicates, the first pair are already shown when the Dupes form loads, and if not, it bypasses the Dupes form and goes straight to the "No more duplicates message."
    If I want to still do that, is there a way to call the button's click event when the form activates? Or should I duplicate the code in the form's activate event? Or can I create a separate sub within the form's module that both the activate event and the click event call to?

(Edit): I think I answered my own questions just by writing them down. I tried the third option, and it seems to work fine. So unless you know of some reason it will make future trouble for me, that's the way I'll leave it.

Thank you very much, Hans.

[HansV]

]Is the reason it works there and not where I had it because the standard module was not affected by the form being unloaded, but if it's within the form's code, the "memory" is erased when the form is unloaded?

Yes, indeed.

I'll have to think about your other question.

[HansV]

It would be helpful if you attached a new sample workbook with both userforms.

[Nick Vittum]

It would be helpful if you attached a new sample workbook with both userforms.

I think our posts crossed in the mail. I'll be happy to strip the data out of the actual workbook and post that if you want (the primary form seems too complicated, code-wise, to do it any other way). But some quick testing now seems to indicate it's going to work well with a separate sub in the form's module—so I won't bother you with it unless you'd like to see it.
Again—thank you.

[HansV]

Hi Nick,

If it's working now, you don't need to post the workbook.