How Do I Loop through Worksheet and Detect Duplicate Lines based on Certain Column Values?-CodePudding

I am writing a macro that will loop through a worksheet to detect duplicate lines in a product log. I need it to look at the UPC (Column A), Expiration Date (Column E), and Location (Column H).

I have the first loop running from 2 to LastRow which is the value of srcSheet.Cells(Rows.Count, "A").End(xlUp).Row. I assign variables the values of Range A/E/H:x and then have a second loop running from LastRow to 3 with a -1 step.

The problem I am having is that when I add a second condition, using either And or a nested If/Then, every line will match. I have that MsgBox output a message and show me the values of x and y.

If I am only checking one pair of values, like chkUPC and srcUPC, I will see everything fine. When I add the second condition, chkExpDate = srcExpDate, it will find every line is a duplicate and when I output the values of x and y, they will match so rather than comparing Row 2 to Row 1950 for the first run, it will be comparing row 3 to iteself.

Here is the code:

srcLastRow = srcSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    For x = 2 To srcLastRow
        srcUPC = srcSheet.Range("A" & x).Value
        srcExpDate = srcSheet.Range("E" & x).Value
        srcQty = srcSheet.Range("G" & x).Value
        srcLocation = srcSheet.Range("H" & x).Value
        
        For y = srcLastRow To 3 Step -1
            
            chkUPC = srcSheet.Range("A" & y).Value
            chkExpDate = srcSheet.Range("E" & y).Value
            chkQty = srcSheet.Range("G" & y).Value
            chkLocation = srcSheet.Range("H" & y).Value
            
            If chkUPC = srcUPC And chkExpDate = srcExpDate Then
                    MsgBox "The same product has been found on multiple lines" & vbCrLf & x & " | " & y
            End If

        Next y
    Next x

CodePudding user response：

Try changing

For y = srcLastRow To 3 Step -1

For y = srcLastRow To x 1 Step -1

otherwise it will compare every line except the second one with itself.