Prevent duplicate value in userform

[mynameisdarn]

Hi,

Im super new here. I have been trying to figure based on the old post. But seems i cant get it right. I tried to copy few codes that i found online to prevent the duplicate values on a data form that i created.

Im trying to avoid duplication for range B2:B299

RowCount = Worksheets("Data2").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Data2").Range("A1")
.Offset(RowCount, 1).Value = Me.txtMembership.Value

Thank you so much!

 

[bugmonsta]

Hi,

In this example I created a basic user form with one text box and one button, the button has the code below behind it

This code counts all your rows using column A, then will check the value in the textbox against column B, if it exists then it will display a message and give you the row number.

I use UCASE here as I used this on names for an entry form, and in case someone types Davidson or Davidson, this search will always match. This will work with numbers as they just don't change case

hope it helps you out

Private Sub CommandButton1_Click()

    Dim search_a As String
    Dim matchFound As Boolean
    Dim matchCount, matchRow, i As Long

search_a = TextBox1.Value
matchFound = False
matchCount = 0
matchRow = 0

    For i = 1 To Cells(Columns(1).Rows.Count, 1).End(xlUp).Row 'counts column A for last row
        If (UCase(search_a) = UCase(Cells(i, 2).Value)) Then 'checks to see if value in column b exists
        matchCount = matchCount + 1
        matchRow = i
        matchFound = True
    End If
Next


If matchFound = False Then
    ElseIf matchCount >= 1 Then
    MsgBox "This value exists!" & vbCr & vbCr & "Please check row " & matchRow
        Else
End If

End Sub