Confused with classes a bit

[next]

I'm trying to accomplish a task with classes, but I'm a bit confused and don't know how to go further. Here's what I have so far, this is the data I'm working with:

Invoice Bulkcode
528020 9134
528018 9134
528019 9134
528021 9134
525950 9135
525947 9135
525948 9135
525949 9135
528022 9136
528023 9137
528024 9137
528026 9137
528025 9137
528027 9138
528029 9138
528028 9138
528030 9138
528031 9139
528032 9139
528033 9139
528034 9139
528037 9140
528035 9140
528036 9140
528038 9140

This is my code that I was able to write so far:
Sub test()
Dim Cell As Range
Dim invoice As cInvoice
Dim Invoices As Collection

Set Invoices = New Collection

For Each Cell In Range("B:B")
If IsEmpty(Cell) Then Exit For
If IsNumeric(Cell) Then
Set invoice = New cInvoice
invoice.BulkNumber = Cell.value
invoice.InvoiceNumber = Cell.Offset(0, -1)
Invoices.Add invoice
End If
Next Cell

For Each invoice In Invoices
Debug.Print invoice.BulkNumber
Next invoice
End Sub
Just in case, here's the class:
Option Explicit

Private pBulkNumber As Double, pInvoiceNumber As Double, pSubTotal As Double, pTax As Double, pTotal As Double
Private pAccountCode As String, pStoreNumber As Double
Private pServiceDate As Date, pServiceDescription As String, pServicePO As String


'Bulk invoice number
Public Property Get BulkNumber() As Variant
BulkNumber = pBulkNumber
End Property
Public Property Let BulkNumber(value As Variant)
pBulkNumber = value
End Property

'Single Invoice number
Public Property Get InvoiceNumber() As Variant
InvoiceNumber = pInvoiceNumber
End Property
Public Property Let InvoiceNumber(value As Variant)
pInvoiceNumber = value
End Property

'SubTotal amount
Public Property Get SubTotal() As Variant
SubTotal = pSubTotal
End Property
Public Property Let SubTotal(value As Variant)
pSubTotal = value
End Property

'Tax Amount
Public Property Get Tax() As Variant
Tax = pTax
End Property
Public Property Let Tax(value As Variant)
pTax = value
End Property

'Gross billing amount
Public Property Get Total() As Variant
Total = pTotal
End Property
Public Property Let Total(value As Variant)
pTotal = value
End Property

'Account Code
Public Property Get AccountCode() As Variant
AccountCode = pAccountCode
End Property
Public Property Let AccountCode(value As Variant)
pAccountCode = value
End Property

'Store Number
Public Property Get StoreNumber() As Variant
StoreNumber = pStoreNumber
End Property
Public Property Let StoreNumber(value As Variant)
pStoreNumber = value
End Property

'Service Date
Public Property Get ServiceDate() As Variant
ServiceDate = pServiceDate
End Property
Public Property Let ServiceDate(value As Variant)
pServiceDate = value
End Property

'Service Description
Public Property Get ServiceDescription() As Variant
ServiceDescription = pServiceDescription
End Property
Public Property Let ServiceDescription(value As Variant)
pServiceDescription = value
End Property

'
Public Property Get ServicePO() As Variant
ServicePO = pServicePO
End Property
Public Property Let ServicePO(value As Variant)
pServicePO = value
End Property


The task here is to accomplish the following: I need to create number of classes, one for each BulkCode; each BulkCode should have associated invoice numbers tied with them. So I want to store and be able to refer to the above data the following way:
Access each bulk code -> first is 9134 -> contains invoices 528020, 528018, 528019 and 528021, each invoice will have billing amounts and dates tied to them.

I'm not sure how to go about it. I can accomplish this with multi dimensional arrays, but it would be such a mess then. Can anyone help?

[Smitty]

Unless I'm missing something, this seems like a pretty good use for a Pivot Table.

[Aflatoon]

If you wish to do this in code, it seems you need a BulkCode class that has a Collection into which you add the Invoices objects; the Invoice class could then have an additional Parent property (returning the BulkCode object). This assumes that you do not have multiple BulkCodes for a given invoice.

[next]

Yeah, can you throw me an example please!?

[Aflatoon]

As a very rough example:

Sub test()
Dim Cell As Range
Dim BulkOrder As CBulkOrder
Dim invoice As CInvoice
Dim Orders As Collection
Dim lngCurrentOrder As Long

Set Orders = New Collection

For Each Cell In Range("B:B")
If IsEmpty(Cell) Then Exit For
If IsNumeric(Cell) Then
If lngCurrentOrder <> Cell.value Then
lngCurrentOrder = Cell.value
Set BulkOrder = New CBulkOrder
BulkOrder.Name = CStr(lngCurrentOrder)
Orders.Add BulkOrder, BulkOrder.Name
End If
Set invoice = BulkOrder.AddInvoice(Cell.Offset(0, -1).value)
Set invoice.Parent = BulkOrder
invoice.BulkNumber = Cell.value
End If
Next Cell

For Each BulkOrder In Orders
Debug.Print BulkOrder.Name
Next BulkOrder
End Sub


CBulkOrder class:
Option Explicit

Private mcolInvoices As Collection
Private m_strName As String

Private Sub Class_Initialize()
Set mcolInvoices = New Collection
End Sub

Private Sub Class_Terminate()
Set mcolInvoices = Nothing
End Sub

Public Property Get Name() As String

Name = m_strName

End Property

Public Property Let Name(ByVal strName As String)

m_strName = strName

End Property

Public Function GetInvoice(lngNumber As Long) As CInvoice

Dim objResult As CInvoice
Set objResult = mcolInvoices(CStr(lngNumber))
Set GetInvoice = objResult
Set objResult = Nothing

End Function
Public Function AddInvoice(lngNumber As Long) As CInvoice

Dim objResult As CInvoice
Set objResult = New CInvoice
mcolInvoices.Add objResult, CStr(lngNumber)
Set AddInvoice = objResult
Set objResult = Nothing

End Function


CInvoice class:
Option Explicit

Private pBulkNumber As Double, pInvoiceNumber As Double, pSubTotal As Double, pTax As Double, pTotal As Double
Private pAccountCode As String, pStoreNumber As Double
Private pServiceDate As Date, pServiceDescription As String, pServicePO As String
Private m_objParent As CBulkOrder

Private Sub Class_Terminate()

Set m_objParent = Nothing

End Sub



Public Property Get Parent() As CBulkOrder

Set Parent = m_objParent

End Property

Public Property Set Parent(objParent As CBulkOrder)

Set m_objParent = objParent

End Property

'Bulk invoice number
Public Property Get BulkNumber() As Variant
BulkNumber = pBulkNumber
End Property
Public Property Let BulkNumber(value As Variant)
pBulkNumber = value
End Property

'Single Invoice number
Public Property Get InvoiceNumber() As Variant
InvoiceNumber = pInvoiceNumber
End Property
Public Property Let InvoiceNumber(value As Variant)
pInvoiceNumber = value
End Property

'SubTotal amount
Public Property Get SubTotal() As Variant
SubTotal = pSubTotal
End Property
Public Property Let SubTotal(value As Variant)
pSubTotal = value
End Property

'Tax Amount
Public Property Get Tax() As Variant
Tax = pTax
End Property
Public Property Let Tax(value As Variant)
pTax = value
End Property

'Gross billing amount
Public Property Get Total() As Variant
Total = pTotal
End Property
Public Property Let Total(value As Variant)
pTotal = value
End Property

'Account Code
Public Property Get AccountCode() As Variant
AccountCode = pAccountCode
End Property
Public Property Let AccountCode(value As Variant)
pAccountCode = value
End Property

'Store Number
Public Property Get StoreNumber() As Variant
StoreNumber = pStoreNumber
End Property
Public Property Let StoreNumber(value As Variant)
pStoreNumber = value
End Property

'Service Date
Public Property Get ServiceDate() As Variant
ServiceDate = pServiceDate
End Property
Public Property Let ServiceDate(value As Variant)
pServiceDate = value
End Property

'Service Description
Public Property Get ServiceDescription() As Variant
ServiceDescription = pServiceDescription
End Property
Public Property Let ServiceDescription(value As Variant)
pServiceDescription = value
End Property

'
Public Property Get ServicePO() As Variant
ServicePO = pServicePO
End Property
Public Property Let ServicePO(value As Variant)
pServicePO = value
End Property

[next]

Thanks