I have a worksheet with 2 columns, the first column contains a quantity,
and second contains a "Unit ID" (string). Any Unit can appear several
times throughout the list. I am wanting to generate another Summary
List and I figured that a collection would be a good way to go, now I
am wondering if this is correct.

I have written a simple test before I actually try to apply it to my
real project, and it just isn't behaving like I expected it to.
Basically I am using the collection to store all the unique unit id's.
The code runs through the whole list and adds the unique id's to the
collection. My hope was that if my script came to a unitid that
already existed in the collection, I could store the current quantity
in a variable, read in the quantity on the new instance, and add it to
the old one and save this quantity in the collection, so I would have a
total for that unit id. Well, when I do this it actually updates the
value on the worksheet as though the collection is directly linked to
the cell. Is this how collections work or am I am just using them
incorrectly. If there is a better way to accomplish this please feel
free to inform me.

using the following values:
1 a
1 a
1 b
1 c
1 c

after I run the script I get this

2 a a 1
1 a a 2
1 b b 1
2 c c 1
1 c c 2

The second group of numbers is just what I am using to see what the
collection has in it.

here is my script...

Private Sub CommandButton1_Click()

Dim foundend As Boolean
Dim ws As Sheet1
Set ws = Worksheets(1)
Dim unitsum As Collection
Set unitsum = New Collection
r = 1
ucol = "B"
While Not foundend
If Not (ws.Range(ucol & r).Text) = "" Then
On Error Resume Next
unitsum.Add ws.Range("A" & r), ws.Range(ucol & r)
'vbNullString , CStr(Range("d" & i + first).Value)
If Err.Number < 0 Then
oqty = unitsum.Item(ws.Range(ucol & r).Value)
unit = ws.Range("B" & r).Text
unitsum.Item(unit) = oqty + ws.Range("A" & r)

End If
ws.Range("D" & r) = ws.Range(ucol & r).Value
ws.Range("E" & r) = unitsum(ws.Range(ucol & r))
Else: foundend = True
End If
r = r + 1
Wend
Set Collection = Nothing

End Sub


--
Dolemite
------------------------------------------------------------------------
Dolemite's Profile: http://www.excelforum.com/member.php...o&userid=26136
View this thread: http://www.excelforum.com/showthread...hreadid=505392