copy to last row from textbox on form to column if it's not duplicate item

[Abdo]

Hello
this code will replace the whole items in column B with textbox1, but what I want copy to the bottom after last row contains item every time try copy to column B .

Private Sub CommandButton1_Click()
Dim Rng As Range
Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    If Application.CountIf(Rng, Trim(TextBox1.Value)) > 0 Then
        MsgBox "Match found in " & TextBox1.Name
        Else
        Rng = TextBox1
    End If
End Sub

how can I fix it, please?

 

[HighAndWilder]

Hello
this code will replace the whole items in column B with textbox1, but what I want copy to the bottom after last row contains item every time try copy to column B .

Private Sub CommandButton1_Click()
Dim Rng As Range
Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    If Application.CountIf(Rng, Trim(TextBox1.Value)) > 0 Then
        MsgBox "Match found in " & TextBox1.Name
        Else
        Rng = TextBox1
    End If
End Sub

how can I fix it, please?

This will write the contents of the textbox to the next empty cell in column B.

Private Sub CommandButton1_Click()
Dim Rng As Range

  Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    
  If Application.CountIf(Rng, Trim(TextBox1.Value)) > 0 Then
    MsgBox "Match found in " & TextBox1.Name
  Else
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = TextBox1
  End If

End Sub

 

[Abdo]

thanks
but shouldn't copy duplicate item!

 

[HighAndWilder]

thanks
but shouldn't copy duplicate item!

What are you wanting to duplicate and how many times?

 

[Abdo]

based on original code will prevent adding duplicate item regardless contains small or big letters for the same item also contains spaces. so there is no limitation after how many times should be duplicates