Listbox counter help

[Guest]

I have two list boxes, (1) that initally holds the data and (1) that can be
added to:
I first fill the List_AddFrom box with items, then check to see if a certain
checkbox is true. If the Checkbox is true, it adds another item ("1st Piece
Sample") into the List_AddFrom Box.

Then I read in items from a sheet into the List_AddTo box. I then compare
the two boxes and remove any duplication from the List_AddFrom box. This
works great unless the the List_AddTo box reads in the "1st Piece Sample"
from the sheet.

Once it compares and removes the "1st Piece Sample" item from the
List_AddFrom box, the next loop gives a List Property array index error (it
comes from the LIst_AddFrom box). This happens regardless of where the "1st
Piece Sample" is located in the box (first, last, middle).

I have rewritten the counters several times (0 to .listcount -1, 0 to
..listcount, .Listcount to 0 step -1) and have even created internal counters
(i = i-1, j = j-1, etc), to counter the error, but to no avail. How can I
fix this? (Code below)

Private Sub Load_NLBoxes()
Dim i As Integer
Dim j As Integer

'Load the List_AddFrom listbox with data
If LAFStr = "" Then
With List_AddFrom
.RowSource = ""
.Clear
.AddItem "Rescinds PEC"
.AddItem "Preventive Action"
.AddItem "UL/CSA/CE Affected"
.AddItem "Manual Change Required"
.AddItem "S/N at Changeover"
.AddItem "Cost Increase over 5%"
End With
CheckSample
Else
Exit Sub
End If

'Validate List_AddFrom Listbox with List_AddTo listbox and remove items as
necessary
' If List_AddFrom.ListIndex = -1 Then Exit Sub
For i = 0 To List_AddFrom.ListCount - 1
For j = 0 To List_AddTo.ListCount - 1
If List_AddTo.List(j) = List_AddFrom.List(i) Then
List_AddFrom.RemoveItem (i)
i = i + 1
End If
Next j
Next i
End Sub

 

[Leith Ross]

Hello asmenut,

The problem lies in removing the item from the collection. The example
will illustrate what happens when an item is remove...



Code
-------------------

LIST BEFORE AN ITEM IS REMOVED LIST AFTER AN ITEM IS REMOVE
Index Value Index Value
0 "Rescinds PEC" 0 "Rescinds PEC"
1 "Preventive Action" 1 "Preventive Action"
2 "UL/CSA/CE Affected" 2 "UL/CSA/CE Affected"
3 "Manual Change Required" 3 "Manual Change Required"
4 "S/N at Changeover" 4 "S/N at Changeover"
5 "Cost Increase over 5%" 5 "Cost Increase over 5%"

-------------------

 

[Tom Ogilvy]

Are we supposed to see a difference? Or are you saying there is no
difference. Maybe I missed it, but the left and right sides look identical.

 

[Guest]

Yes there is a difference. Let's say that the sheet had the following data
that was read into the List_AddTo box:

Preventive Action
Rescinds PEC
1st Piece Sample Required

The Code compares the items and if the strings match, then the item in the
List_AddFrom box is removed. Based upon the original List_AddFrom list:

Rescinds PEC
Preventive Action
UL/CSA/CE Affected
Manual Change Required
S/N at ChangeOver
Cost Increase over 5%
Iset Piece Sample Required (This is if CheckSample is true)

and comparing the "read in" items (List_AddFrom), the resulting lists should
be as follows:

Index List_AddFrom Value Index List_AddTo Value
0 "UL/CSA/CE Affected" 0 "Preventive Action"
1 "Manual Change Required" 1 "Rescinds PEC"
2 "S/N at Changeover" 2 "1st Piece Sample
Required"
3 "Cost Increase over 5%"

Are we supposed to see a difference? Or are you saying there is no
difference. Maybe I missed it, but the left and right sides look identical.

 

[Leith Ross]

Hello asmenut,

Change the validation loop code to this...


Code:
--------------------
Dim N As Long

For j = 0 To List_AddTo.ListCount - 1
If List_AddTo.List(j) = List_AddFrom.List(N) Then
List_AddFrom.RemoveItem (N)
Else
N = N + 1
End If
Next j

 

[Guest]

Leith,

Thanks. That's the ticket. Once I read through your modification, I
realized that I made a major error doing a dual "For/Next" loop. One loop and
a separate counter was the more correct way to approach the problem. It is
good to learn.

Experience comes from making mistakes, wisdom comes from not repeating them.