Need help with indirect variable naming

[greenythebeast]

Hi all,

I am struggling to figure out how to accomplish indirect variable naming. I've included my code below (feel free to offer any tips!). Essentially, I am going through a worksheet that has purchase orders and I'm trying to create a collection of all unique PO numbers along with their info. Instead of all SKUs being on one line for each PO, a new line with the same PO number is added when there are more than 1 SKU on the order. The code I've written below does a decent job when there is 1 SKU, but I can't figure out how best to integrate more than 1 SKU. I've essentially made it so if the key (poNumber) doesn't exist, add a new class member, otherwise update the SKU2 property of the existing key. How can I update my code so that if there is more than 2 SKUs it updates the correct property? Same with skuCount. Appreciate all your help!

P.S. Is a collection the best way to do this? Would a dictionary work better?

packersPO class module:

Option Explicit

'List of class properties
Public poNumber As String
Public poDate As Date
Public poOrderNumber As String
Public poShipTo As String
Public poPhone As String
Public poAddress1 As String
Public poAddress2 As String
Public poAddress3 As String
Public poZIP As String
Public poCity As String
Public poState As String
Public poCountry As String
Public poSKU1 As String
Public poQty1 As Long
Public poSKU2 As String
Public poQty2 As Long
Public poSKU3 As String
Public poQty3 As Long
Public poSKU4 As String
Public poQty4 As Long
Public poSKU5 As String
Public poQty5 As Long
Public poDescription As String
Public poSize As String
Public poColor As String
Public poCost As Currency
Public poPersonalization1 As String
Public poPersonalization2 As String
Public poPersonalization3 As String
Public skuCount As Long

Module:

Public Sub POClass()

Dim coll As collection
Dim dataItems As packersPO
Dim poNumber As String
Dim ws As Worksheet
Dim r As Long

Set ws = ActiveSheet
Set coll = New collection

Dim lastRow As Long
lastRow = ws.Cells(Rows.count, "A").End(xlUp).Row

For r = 2 To lastRow

    'Retrieve PO Number for key assignment
    poNumber = CStr(ws.Cells(r, "A").Value2)

    'Check if key already exists
    Set dataItems = Nothing: On Error Resume Next
    Set dataItems = coll(poNumber): On Error GoTo 0

    'If key doesn't exist, create a new class object
    If dataItems Is Nothing Then
        Set dataItems = New packersPO
        dataItems.poNumber = CStr(ws.Cells(r, "A").Value2)
        dataItems.poDate = CDate(ws.Cells(r, "B").Value2)
        dataItems.poOrderNumber = CStr(ws.Cells(r, "C").Value2)
        dataItems.poShipTo = CStr(ws.Cells(r, "D").Value2)
        dataItems.poPhone = CStr(ws.Cells(r, "E").Value2)
        dataItems.poAddress1 = CStr(ws.Cells(r, "F").Value2)
        dataItems.poAddress2 = CStr(ws.Cells(r, "G").Value2)
        dataItems.poAddress3 = CStr(ws.Cells(r, "H").Value2)
        dataItems.poZIP = CStr(ws.Cells(r, "I").Value2)
        dataItems.poCity = CStr(ws.Cells(r, "J").Value2)
        dataItems.poState = CStr(ws.Cells(r, "K").Value2)
        dataItems.poCountry = CStr(ws.Cells(r, "L").Value2)
        dataItems.poSKU1 = CStr(ws.Cells(r, "M").Value2)
        dataItems.poQty1 = CLng(ws.Cells(r, "N").Value2)
        dataItems.poDescription = CStr(ws.Cells(r, "O").Value2)
        dataItems.poSize = CStr(ws.Cells(r, "P").Value2)
        dataItems.poColor = CStr(ws.Cells(r, "Q").Value2)
        dataItems.poCost = CCur(ws.Cells(r, "R").Value2)
        dataItems.poPersonalization1 = CStr(ws.Cells(r, "S").Value2)
        dataItems.poPersonalization2 = CStr(ws.Cells(r, "T").Value2)
        dataItems.poPersonalization3 = CStr(ws.Cells(r, "U").Value2)
        dataItems.skuCount = 1
        coll.Add dataItems, poNumber
    Else
        dataItems.poSKU2 = CStr(ws.Cells(r, "M").Value2)
        dataItems.poQty2 = CStr(ws.Cells(r, "N").Value2)
    End If

Next

End Sub

Also asked here Need help with indirect variable naming