PDA

View Full Version : [SOLVED] Ensure Selected Item in Listbox does not exist in Range



swaggerbox
03-26-2018, 04:51 AM
I have a userform with a listbox that allows me to select multiple items. The selected items (only the first column is exported; the second column is for display only) are placed in column K. Now my problem is how to I ensure that the items that I select is not a duplicate of a value already found on column K? Anyone got ideas?




Private Sub CommandButton1_Click()


Dim lngItem As Long


For lngItem = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(lngItem) Then
With Sheets("SAMPLING")
.Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0).Value = ListBox1.List(lngItem, 0)
End With
End If

Next lngItem

End Sub

SamT
03-26-2018, 08:07 AM
Private Sub CommandButton1_Click()
Dim lngItem As Long
Dim strArg As String
Dim rngFound As Range


For lngItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lngItem) Then
strArg = ListBox1.Selected(lngItem)
With Sheets("SAMPLING")
Set rngFound = .Range("K:K").Find strArg
If rngFound is Nothing then _
.Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0).Value = strArg
End With
End If
Next lngItem
End Sub

swaggerbox
03-27-2018, 01:08 AM
Thanks Sam