found duplicate value in textbox and dont allow to insert column A

[mrchesser]

Hello, I wrote this code and it checks in the text box that there is no duplicate data to be added and it works correctly (the input values in the text box are multiple and entered below each other)
I want to have a code that checks if the value already exists in column A and shows it and does not just add the same value and adds the rest.
tank u

Private Sub CommandButton3_Click()
    Dim Target As Range
    Dim Data As Variant
    Dim DuplicateNumbers As String
    Dim i As Long, j As Long
    Dim DuplicateFound As Boolean
    
    If TextBox1.Text = "" Then Exit Sub
    Data = Split(TextBox1.Text, vbCrLf)
    
    With Worksheets("sheet1")
        Set Target = .Range("A" & .Rows.Count).End(xlUp)
        If Target.Value <> "" Then Set Target = Target.Offset(1)
        
        ' Check for duplicate numbers
        For i = LBound(Data) To UBound(Data)
            DuplicateFound = False
            For j = i + 1 To UBound(Data)
                If Data(i) = Data(j) Then
                    DuplicateNumbers = DuplicateNumbers & Data(i) & vbCrLf
                    DuplicateFound = True
                    Exit For
                End If
            Next j
            If DuplicateFound = False Then
                Target.Value = Data(i)
                Set Target = Target.Offset(1)
            End If
        Next i
    End With
    
    ' Display duplicate numbers
    If DuplicateNumbers <> "" Then
        MsgBox "Duplicate Numbers:" & vbCrLf & DuplicateNumbers
    End If
End Sub

Book1 (1).xlsm
	A	B	C	D
1	number
2	2
3	3
4	4
5	5
6	43
7	44
8	2
9	2
10	2
11	3
12	3
13
Sheet1

 

[Legacy 143009]

Hi,

This should work:

Private Sub CommandButton3_Click()
    Dim Target As Range
    Dim Data As Variant
    Dim DuplicateNumbers As String
    Dim i As Long, j As Long
    Dim DuplicateFound As Boolean
    Dim duplicates As String
    If TextBox1.Text = "" Then Exit Sub
    Data = Split(TextBox1.Text, vbCrLf)
   
    With Worksheets("Sheet1")
        Set Target = .Range("A" & .Rows.Count).End(xlUp)
        If Target.Value <> "" Then Set Target = Target.Offset(1)
       
        ' Check for duplicate numbers
        For i = LBound(Data) To UBound(Data)
            DuplicateFound = False
            For j = i + 1 To UBound(Data)
                If Data(i) = Data(j) Then
                    DuplicateNumbers = DuplicateNumbers & Data(i) & vbCrLf
                    DuplicateFound = True
                    Exit For
                End If
            Next j
            If DuplicateFound = False Then
              If IsError(Application.Match(CDbl(Data(i)), .Range("A:A"), 0)) Then
                Target.Value = Data(i)
                Set Target = Target.Offset(1)
              Else
                duplicates = duplicates & Data(i) & ", "
              End If
            End If
           
        Next i
        duplicates = Left(duplicates, Len(duplicates) - 2)
        MsgBox duplicates & " are duplicate values."
    End With
   
    ' Display duplicate numbers
    If DuplicateNumbers <> "" Then
        MsgBox "Duplicate Numbers:" & vbCrLf & DuplicateNumbers
    End If
End Sub

 

[mrchesser]

@

Flashbond​

If we repeat the new number twice in the form, I get an error. Can you fix it?TANK U

 

[mrchesser]

@Flashbond​

If we repeat the new number twice in the form, I get an error. Can you fix it?TANK U
Is it possible to write the code so that no data is placed in column A until duplicate data is deleted from column A or from inside the text box?

 

[Legacy 143009]

I made small modifications but the code is working totally fine for me.
I start like below

First warning is like this, no problem:

Second warning is like this, again no problem:

Please find the latest code below:

Private Sub CommandButton3_Click()
    Dim Target As Range
    Dim Data As Variant
    Dim DuplicateNumbers As String
    Dim i As Long, j As Long
    Dim DuplicateFound As Boolean
    Dim duplicates As String
    If TextBox1.Text = "" Then Exit Sub
    Data = Split(TextBox1.Text, vbCrLf)
 
    With Worksheets("sheet1")
        Set Target = .Range("A" & .Rows.Count).End(xlUp)
        If Target.Value <> "" Then Set Target = Target.Offset(1)
    
        ' Check for duplicate numbers
        For i = LBound(Data) To UBound(Data)
            DuplicateFound = False
            For j = i + 1 To UBound(Data)
                If Data(i) = Data(j) Then
                    DuplicateNumbers = DuplicateNumbers & Data(i) & vbCrLf
                    DuplicateFound = True
                    Exit For
                End If
            Next j
            If DuplicateFound = False Then
              If IsError(Application.Match(CDbl(Data(i)), .Range("A:A"), 0)) Then
                Target.Value = Data(i)
                Set Target = Target.Offset(1)
              Else
                duplicates = duplicates & Data(i) & ", "
              End If
            End If
        
        Next i
        If Len(duplicates) > 0 Then
          duplicates = Left(duplicates, Len(duplicates) - 2)
          Select Case UBound(Split(duplicates, ","))
          Case 0
            MsgBox duplicates & " is already in the sheet."
          Case Else
            MsgBox duplicates & " are already in the sheet."
          End Select
        End If
    End With
 
    ' Display duplicate numbers
    If DuplicateNumbers <> "" Then
        MsgBox "Duplicate Numbers in Form:" & vbCrLf & DuplicateNumbers
    End If
End Sub

Please share a sample scenario if you are still having problems.

 

[mrchesser]

@Flashbond barvo man that is work​

If a space is added after entering the numbers in the text box and an empty space is created, it will give error 13
Another issue
if i want after entering the numbers in textbox and When pressing the button, the form2 opens, and the caption of that button1 which is in form2 is placed in front of the numbers entered in column B. What should I do?

 

[Legacy 143009]

and the caption of that button1 which is in form2 is placed in front of the numbers entered in column B.

I was not able to imagine this in my mind. Can you share a screenshot please?

For the other issue try this:

Private Sub CommandButton3_Click()
    Dim Target As Range
    Dim Data As Variant
    Dim DuplicateNumbers As String
    Dim i As Long, j As Long
    Dim DuplicateFound As Boolean
    Dim duplicates As String
    If TextBox1.Text = "" Then Exit Sub
    Data = Split(TextBox1.Text, vbCrLf)
 
    With Worksheets("sheet1")
        Set Target = .Range("A" & .Rows.Count).End(xlUp)
        If Target.Value <> "" Then Set Target = Target.Offset(1)
    
        ' Check for duplicate numbers
        For i = LBound(Data) To UBound(Data)
            Data(i) = Trim(Data(i))
            DuplicateFound = False
            For j = i + 1 To UBound(Data)
                If Data(i) = Trim(Data(j)) Then
                    DuplicateNumbers = DuplicateNumbers & Data(i) & vbCrLf
                    DuplicateFound = True
                    Exit For
                End If
            Next j
            If DuplicateFound = False Then
              If IsError(Application.Match(CDbl(Data(i)), .Range("A:A"), 0)) Then
                Target.Value = Data(i)
                Set Target = Target.Offset(1)
              Else
                duplicates = duplicates & Data(i) & ", "
              End If
            End If
        Next i
        If Len(duplicates) > 0 Then
          duplicates = Left(duplicates, Len(duplicates) - 2)
          Select Case UBound(Split(duplicates, ","))
          Case 0
            MsgBox duplicates & " is already in the sheet."
          Case Else
            MsgBox duplicates & " are already in the sheet."
          End Select
        End If
    End With
 
    ' Display duplicate numbers
    If DuplicateNumbers <> "" Then
        MsgBox "Duplicate Numbers in Form:" & vbCrLf & DuplicateNumbers
    End If
End Sub

 

[mrchesser]

@Flashbond tank u for answer bro​

i have 3 form(1,2,3)
after insert number in textbox1 and prees insert button i want to show form2 to select one of the cabinet that set in (range of textbox number) in column B

and after show form3 to select one of the drawer to insert in (range of textbox number) column C
Because it is possible to enter duplicate numbers, it is better that if there is a duplicate number, no value should be added in column A, and when there is no duplicate data message, form 2 and form 3 should be shown in order to determine the location (cabinet and drawer) for the entered numbers.

 

[mrchesser]

@Flashbond​

this is better if you can help me
in text box front cabinet in column B
and the option button in column C

if you click on the picture can downland my file

 

[Legacy 143009]

I think I understand what you want.
So you won't use form2 and form3?