Pasting from listbox to spreadsheet - is there a way to avoid duplicate entries?

[VBAhelp33]

Hi all

I am pasting data from 2 columns in a listbox to an excel spreadsheet. Is there a way I can avoid duplicate entries into the worksheet if they are already entered? I would also like to be able to tell the user which entry is a duplicate.

Many thanks

Private Sub CommandButton4_Click() 'paste selected items in listbox2 to excel worksheet

Application.ScreenUpdating = False

Dim I As Long
For I = 0 To Me.ListBox2.ListCount - 1
  ListBox2.Selected(I) = True
    
Next

Dim rngNext As Range
Dim col As Long

    Set rngNext = Worksheets("data").Range("E" & Rows.Count).End(xlUp).Offset(1)
    For I = 0 To ListBox2.ListCount - 1
    
        If ListBox2.Selected(I) Then
            For col = 0 To ListBox2.ColumnCount - 1
                rngNext.Offset(, col).Value = ListBox2.List(I, col)
            Next col
            
            Set rngNext = rngNext.Offset(1)
            ListBox2.Selected(I) = False
            
        End If
            
    Next I
       

Application.ScreenUpdating = True

BeforeExit:

Set rngNext = Nothing

 

[JLGWhiz]

Something like this

For col = 0 To ListBox2.ColumnCount - 1
    If Application.CountIf(Columns(col), ListBox2.List(I, col)) = 0 Then
                rngNext.Offset(, col).Value = ListBox2.List(I, col)
    Else
        MsgBox "Duplicate detected in column  " & col
        'Add code here to Exit sub, go to handler, etc.
    End If
Next col

 

[VBAhelp33]

Thanks for your reply
What should Columns be?

If Application.CountIf(COLUMNS(col), ListBox2.List(I, col)) = 0 Then

 

[JLGWhiz]

You stated you wanted to check for duplicate entries, but did not specify where. The code I suggested would check each column that you are addressing to see if it contains the same value you are about to enter into the cell.