Compare Contents of 2 listboxes

L

LaDdIe

Could I please have assistance with the code below, my aim is to compare
contents of ListBox2 with ListBox1 if a match is found to remove it from
ListBox1;

Dim i As Long, j As Long

For i = 0 To ListBox2.ListCount - 1
DoEvents
ListBox2.ListIndex = i
For j = 0 To ListBox1.ListCount - 1
DoEvents
ListBox1.ListIndex = j
If InStr(1, ListBox2.List(ListBox2.ListIndex),
ListBox1.List(ListBox1.ListIndex)) Then
ListBox1.RemoveItem (j)
End If
Next j
Next i

End Sub
 
D

Dave Peterson

This worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim res As Variant
Dim myArr() As String

With Me.ListBox2
ReDim myArr(0 To .ListCount - 1)
For iCtr = 0 To .ListCount - 1
myArr(iCtr) = .List(iCtr, 0)
Next iCtr
End With

With Me.ListBox1
'bottom up
For iCtr = .ListCount - 1 To 0 Step -1
res = Application.Match(.List(iCtr), myArr, 0)
If IsError(res) Then
'no match, keep it
Else
.RemoveItem iCtr
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.ColumnCount = 1
For iCtr = 1 To 5
.AddItem "a" & iCtr
Next iCtr
For iCtr = 9 To 15
.AddItem "a" & iCtr
Next iCtr
End With

With Me.ListBox2
.ColumnCount = 1
For iCtr = 3 To 7
.AddItem "a" & iCtr
Next iCtr
End With
End Sub
 
J

Jim Cone

'Sub can be called from a UserForm event.
'Assumes the Listboxes are not bound to a worksheet range.
'Assumes data is loaded into Listboxes before this sub is called...
'--
Sub MakeDifferent()
Dim i As Long
Dim j As Long

For i = 0 To UserForm1.ListBox2.ListCount - 1
For j = UserForm1.ListBox1.ListCount - 1 To 0 Step -1
If UserForm1.ListBox2.List(i, 0) = _
UserForm1.ListBox1.List(j, 0) Then
UserForm1.ListBox1.RemoveItem (j)
End If
Next 'j
Next 'i
End Sub
--
Jim Cone
Portland, Oregon USA



"LaDdIe"
<[email protected]>
wrote in message
Could I please have assistance with the code below, my aim is to compare
contents of ListBox2 with ListBox1 if a match is found to remove it from
ListBox1;

Dim i As Long, j As Long

For i = 0 To ListBox2.ListCount - 1
DoEvents
ListBox2.ListIndex = i
For j = 0 To ListBox1.ListCount - 1
DoEvents
ListBox1.ListIndex = j
If InStr(1, ListBox2.List(ListBox2.ListIndex),
ListBox1.List(ListBox1.ListIndex)) Then
ListBox1.RemoveItem (j)
End If
Next j
Next i

End Sub
 
L

LaDdIe

Supa Dupa..... Thanks

Dave Peterson said:
This worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim res As Variant
Dim myArr() As String

With Me.ListBox2
ReDim myArr(0 To .ListCount - 1)
For iCtr = 0 To .ListCount - 1
myArr(iCtr) = .List(iCtr, 0)
Next iCtr
End With

With Me.ListBox1
'bottom up
For iCtr = .ListCount - 1 To 0 Step -1
res = Application.Match(.List(iCtr), myArr, 0)
If IsError(res) Then
'no match, keep it
Else
.RemoveItem iCtr
End If
Next iCtr
End With

End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.ColumnCount = 1
For iCtr = 1 To 5
.AddItem "a" & iCtr
Next iCtr
For iCtr = 9 To 15
.AddItem "a" & iCtr
Next iCtr
End With

With Me.ListBox2
.ColumnCount = 1
For iCtr = 3 To 7
.AddItem "a" & iCtr
Next iCtr
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top