PDA

View Full Version : removing items from collections



jpap1
10-23-2009, 04:00 AM
hi,

i have created a list where there are several cells that describe the item and am reading them into separate collections. The idea is that i then remove an item, put it into a new colelction and compare it to the old collection(from where it was removed to see if there are any others) and add the others to the new collection. for example say i ahve a list of 3 items (a, b, a) i want to add that to a collection with the end result being 2 new collections with one containing 2 lots of a and the other 1 lot of b

This is the code i have so far but im getting an error at 'ncrlist.add item' - im not sure why there is this error - although my memory of collections is quite rusty... could any one please shed some light?

many thanks


Sub abc()
Dim credit() As String

For i = 1 To 60000
If Range("credit").Offset(i, 0).Value = "" Then
counter = counter
Else
counter = counter + 1
End If
Next
ReDim credit(1 To counter)

For i = 1 To counter
credit(i) = Range("credit").Offset(i, 0).Value
Next

Dim creditlist As Collection, ncrlist as Collection

For i = 1 To counter
v = v + 1
creditlist.Add credit(i)
Next

For i = 1 To counter
Item = creditlist.Count
creditlist.Remove (Item)
ncrlist.Add Item
j = j + 1
If ncrlist(j) = creditlist(i) Then
creditlist.Remove (credit)
ncrlist.Add credit(i)
End If
Next

Aflatoon
10-23-2009, 06:47 AM
You haven't initialised either Collection variable, so you should be getting an error earlier than that, on this line:

creditlist.Add credit(i)

jpap1
10-23-2009, 07:13 AM
ok ive made this even simpler and im still gettign an error that i cant figure out (sorry but my knowledge of colelctions is very hazy)

i have set creditlist and ncrlist higher in code and i am simply adding data to creditlist then removing it from there and adding it to ncrlist... but am getting 'invalid procedure, call or argument' on line 'creditlist.remove numname'- what does that refer to?

For i = 1 To counter

creditlist.Add credit(i)

Next

For i = 1 To counter
num = creditlist.Count
numname = creditlist(num)
creditlist.Remove numname
ncrlist.Add numname
Next

Aflatoon
10-23-2009, 07:18 AM
You have not specified a key when adding the item to the collection:


For i = 1 To counter
' specify the key to be the same as the value
creditlist.Add credit(i), credit(i)

Next

For i = 1 To counter
num = creditlist.Count
numname = creditlist(num)
' either use the index number
creditlist.Remove num
' or the key value:
'creditlist.Remove numname
ncrlist.Add numname
Next

jpap1
10-23-2009, 07:48 AM
with regards to creditlist.add credit(i), credit(i) it comes up with an error saying part of it is already being used in the colelction - i then tried using the loop counter 'i' and that gets a type mismatch, then i tried another counter such as 'v=v+1' inside the loop and that came up with a type mismatch as well - i really have no idea why none of these are being accepted

Aflatoon
10-23-2009, 08:04 AM
The key needs to be a unique string value (not a number). Are you adding duplicate items to the collection? If so, use CStr(i) as the key.

jpap1
10-23-2009, 08:07 AM
yep - it works - pleasae can you explain what CStr(i) does so i can learn to avoid the same pitfall in the future? thanks

Aflatoon
10-23-2009, 08:15 AM
It just converts the number to a string representation of the number - so 1 becomes "1" for example.