If duplicate value found then MsgBox & remove duplicate

    I am trying to write a piece of code that when a new record is entered it will prompt a duplicate scan, and if a duplicate is found execute a msgbox that says something like " Error" & value of duplicate " is a duplicate value please enter a valid entry" But only if there is a duplicate entry entered into the excel table. I have looked around and found a lot of code similar to this but nothing that really seems to be executing exactly what I want to happen. any help or suggestions would be most appreciated.

    Re: If duplicate value found then MsgBox & remove duplicate


    There's probably a whole lot of ways to do that, but without seeing your workbook it's hard to write code.


    One way is if you are entering a value in a cell in column A, then worksheet change event code could check all the cells in column A above where you are entering a new entry and use a countif function to see if that value already exists.


    Again that's just one example but without a workbook and knowing how you are entering new records it's hard to just write code that will work with what you have.

    Bruce :cool:

    Re: If duplicate value found then MsgBox & remove duplicate


    What I got so far is this:





    Code
    Sub GoDupe()
Cells.RemoveDuplicates Columns:=Array(1), Header:=xlNo
Range("A65536").End(xlUp).Offset(1, 0).Select
End Sub


    My code removes duplicates no problem, however I want it to prompt a msgbox if there is a duplicate within the table, if there is no duplicate I want it to do nothing.


    Moderator Comment: Please reread the rules you agreed to when you joined the forum and use code tags when ever posting VBA in a thread.

    Re: If duplicate value found then MsgBox & remove duplicate


    Your original post says when you enter a new record, but now you post code that copies six cells to another sheet?


    I don't really know what you are looking to do.


    Perhaps you could post an sample workbook.
    [sw]*[/sw]

    Bruce :cool:

    Re: If duplicate value found then MsgBox & remove duplicate


    Sorry about that I have been going back between access and excel, making a sample interface. By record I just meant row, but anyways I uploaded the workbook, check sheet 2 as sheet 1 will always be empty as you will see its only purpose is to input data then copy and paste to sheet 2 which is linked to access. If it seems confusing just let me know and I can try to better explain. But I am trying to get the goDupe Sub to check for duplicates in sheet 2 and then if there is a duplicate prompt a message box that says "duplicate entry" and then remove the duplicate. The help is most appreciated. The unique values are contained in column A, all other columns may have duplicate values. The help and or suggestions is most appreciated.

    Re: If duplicate value found then MsgBox & remove duplicate


    If column A of sheet2 has unique values then before you even paste anything over to column A of sheet2 use the countif function in VBA code to check if the value already exists.
    Let's say sheet1 cell A1 has this unique value.

    Code
    Sub Test()
Dim x As Long
x = WorksheetFunction.CountIf(Worksheets("Sheet2").Columns(1), Worksheets("Sheet1").Range("A1"))
If x > 0 Then
    MsgBox "Duplicate"
Else
    'not a duplicate do something else.
End If
End Sub


    Alternate version, no variable needed.

    Code
    Sub Test()
If WorksheetFunction.CountIf(Worksheets("Sheet2").Columns(1), Worksheets("Sheet1").Range("A1")) Then
    MsgBox "Duplicate"
Else
    'not a duplicate do something else.
End If
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login