I think you are missing a line in your loop:

Dim MyRecord as Record
For Each Cell in Selection
Set MyRecord = New Record ' << missing line
MyRecord.OrdernNmber = Cell.Value
MyCollection.Add MyRecord
Next

--

Rob wrote in message ...
I've made a class module and I'm tring to add my objects to a collection, but I can't seem to get the code right. I've set up my class module like below, and in my sub I want to loop through a list of order numbers and add one of my class module records to my collection. The problem is that only one record gets stored, multiple times.

Thanks for any help
Rob

'Class Module "Record" Code
Public OrderNumber as long

Function OrderDate() As Date
OrderDate = FetchOrderDate(OrderNumber)
End Function

Function DeliveryDate() As Date
DeliveryDate = FetchDeliveryDate(OrderNumber)
End Function
'End of Class Module "Record" Code

'Module Code
Sub StoreRecords
Dim MyCollection as New Collection
Dim MyRecord as Record
For Each Cell in Selection
MyRecord.OrdernNmber = Cell.Value
MyCollection.Add MyRecord
Next

'now I test whether it worked by getting it to tell me the order numbers stored
For Each Record in MyCollection
MsgBox Record.OrderNumber
Next

End Sub