Use my existing code but alter part of it

[ipbr21054]

I am using the code below to inform me of any duplicates in column AB of which it works well.
I run the code & if any duplicates are found i see the Msgbox saying DUPLICATE KEY CODE FOUND IN CELL AB27
Then i click OK & the message box will close.

The alteration i need please is for me to still be advised of the cell location BUT when i click OK the code then needs to continue down the column as opposed to exiting & once done a Msgbox with something like search complete etc

Example.
DUPLICATE KEY CODE FOUND IN CELL AB27
I click OK then the code carries on down the page.

DUPLICATE KEY CODE FOUND IN CELL AB54
I click OK then the code carries on down the page.

DUPLICATE KEY CODE FOUND IN CELL AB99
I click OK then the code carries on down the page.

Private Sub DuplicateKeyCode_Click()
    Dim cell As Range

    With Intersect(ActiveSheet.Columns("AB"), ActiveSheet.UsedRange)
        For Each cell In .Cells
            If WorksheetFunction.CountIf(.Resize(cell.Row - .Rows(1).Row + 1), cell.Value) > 1 Then
             MsgBox "DUPLICATE KEY CODE " & cell.Value & "  FOUND IN CELL " & cell.Address(False, False), vbCritical, "DUPLICATE ITEM NUMBER CHECKER"
             cell.Select
             Exit Sub
           End If
            
        Next cell

    End With
    MsgBox "NO DUPLICATES WERE FOUND", vbInformation, "DUPLICATE KEY CODE CHECKER"

End Sub

 

[offthelip]

try this:

Private Sub DuplicateKeyCode_Click()
    Dim cell As Range
   dupfound = False
  With Intersect(ActiveSheet.Columns("AB"), ActiveSheet.UsedRange)
        For Each cell In .Cells
            If WorksheetFunction.CountIf(.Resize(cell.Row - .Rows(1).Row + 1), cell.Value) > 1 Then
             cell.Select
             answer = MsgBox("DUPLICATE KEY CODE " & cell.Value & "  FOUND IN CELL " & cell.Address & vbNewLine & "Do you want to continue?", vbYesNo + vbQuestion, "DUPLICATE ITEM NUMBER CHECKER")
             dupfound = True
             If answer = vbNo Then
                  Exit Sub
             End If
           End If
          
        Next cell

    End With
    If Not (dupfound) Then
    MsgBox "NO DUPLICATES WERE FOUND", vbInformation, "DUPLICATE KEY CODE CHECKER"
    Else
   Msgbox "Complete"
    End If
End Sub

 

[ipbr21054]

Hi,
Thanks for that.

It works BUT i see $ signs around the cell reference. $AB$15 not AB15 ??
See attached photo.
Thanks

 

[Joe4]

Hi,
Thanks for that.

It works BUT i see $ signs around the cell reference. $AB$15 not AB15 ??
See attached photo.
Thanks

View attachment 68821

If you want to get rid of the dollar signs, just replace the "cell.Address" of this line to match your format you used in your original code, i.e.
change this:

answer = MsgBox("DUPLICATE KEY CODE " & cell.Value & "  FOUND IN CELL " & cell.Address & vbNewLine & "Do you want to continue?", vbYesNo + vbQuestion, "DUPLICATE ITEM NUMBER CHECKER")

to this:

answer = MsgBox("DUPLICATE KEY CODE " & cell.Value & "  FOUND IN CELL " & cell.Address(False, False) & vbNewLine & "Do you want to continue?", vbYesNo + vbQuestion, "DUPLICATE ITEM NUMBER CHECKER")

 

[ipbr21054]

Thanks very much.

 

[ipbr21054]

@offthelip thanks for the help with the code.
Just what I needed.