Prevent duplicate entries

[Julee]

Dear all,

I need to validate a particular cell, let's say A20, in the sheet1 that it would check all column B in the sheet2 whether there already exists such a text. If such a text already exists in column B of the sheet2, I need that it would inform the user with the MsgBox that such a text already exist. Is it possible to do this with excel VBA?


Thanks a lot!

 

[ChrisBM]

Try using the on change function.

Private Sub Worksheet_Change(ByVal TargetCell as Range)
       If [COLOR=#333333]Application.WorksheetFunction.IsNA[/COLOR]([COLOR=#333333]Application.WorksheetFunction.Match([/COLOR]TargetCell.Value, Sheets("Sheet2").Range("B:B"), 0)) Then
             'Fine, no matches
       Else
             Msgbox("Already exists!") 
             'Clear Cell
             TargetCell.Value = ""
       End If
End Sub

 

[alansidman]

Put this code in the worksheet events. Open the VBE and click on the worksheet in the VBA project window and then insert the code in the right window when it opens.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
    If Target.Column = 1 Then
        For i = 1 To lr
        If Range("B" & i) = Target.Value Then
        MsgBox "Duplicate"
        End If
        Next i
    End If
End Sub

 

[Julee]

Thank you very much for the codes, but it doesn't work.. What could be the reason?
Where do I indicate that particular cell, i.e. "A20" that I would like to validate?

 

[dcofer]

Does the solution have to be a VBA solution? If not, you can use data validation.

Click on cell A20, then go to the Data tab, and then click on Data Validation.

Select Custom in the drop down box under Allow, and enter this formula in the formula box: =COUNTIF(B:B,A20)<1

This will prevent anyone from entering data in cell A20 that matches exactly any data in Column B.

You can then click on the Input Message tab to customize the message in the message box that pops up.

Hope this helps.

 

[alansidman]

Julee
It worked for me when I tested the code. Where did you place the code? Did you follow my instructions and place it in a worksheet event? NOT in a module.

This line in the code identifies that you are looking at column A

 If Target.Column = 1 Then</pre>

 

[Julee]

I need to make a reference to the different sheet. When I select Custom in the Data Validation, it gives me an error saying that "You cannot use references to other worksheets or workbooks for Data Validation criteria".

Yes, I place it in the worksheet event indeed. However, I need a cell "A20" in sheet1 to be checked in the whole column "B" in the sheet2 if there are no duplicates. Would you be able to let me know, how both these sheets could be linked?

 

[alansidman]

changed it up to reflect input in A20 on sheet 1 and data already in sheet 2

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim i As Long
Dim lr As Long
lr = s2.Range("B" & Rows.Count).End(xlUp).Row
    If Target.Address = "$A$20" Then
        For i = 1 To lr
        If s2.Range("B" & i) = Target.Value Then
        MsgBox "Duplicate"
        End If
        Next i
    End If
End Sub

 

[kvsrinivasamurthy]

Sheet2 B2:B50 is reference data. Name that range as Range2 (using Name manager).
Validate Sheet1 A20 cell with
Custom
Countif(Range2,A20)=0

In Error message type

Text already exists.

 

[Julee]

Thank you so much, but none of them is working for me... What am I missing?