ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
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.
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.
Rich (BB code):
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