hi

i have had a go at trying to develop the above, but my limited knowledge of dictionaries is holding me back!

What im trying to do is as follows. I have a worksheet with several rows of data spanning 6 columns. The fourth column contains a batch code, which may be repeated on other rows. I need to use this batch code as an order number for all rows that have it.

So, i use a collection to get all the unique batch numbers in the sheet, and for each one, i want to create a dictionary with the batch code as the key and an array of rows as the item. This forms one order, and i want to write it to another worksheet later.

I then want to collect these individual orders into another dictionary, which im hoping i can loop over later to read out the individual orders. The problem im having is, i cant see the arrays saved in the dictionaries (or i just dont know how to), and dont know the best way to iterate over them.

I can visualise what im trying to achieve, but dont know if im going about this the right way or if theres a better way, so i would appreciate some help. My attempt at this is attached.

thanks


Sub CreateOrders()
Dim var() As Variant, coll As New Collection, r As Long, c As Long, count As Long
Dim dictSingle As Dictionary, i As Long, dictOrders As New Dictionary

Application.ScreenUpdating = False

 With wksSingleZone
    
    'get no of unique batch codes
        For r = 2 To .Cells(Rows.count, 1).End(xlUp).row
            On Error Resume Next
                coll.Add .Cells(r, 4), CStr(.Cells(r, 4))
            On Error GoTo 0
        Next r
     
    'For each batch code, get its item details
     For i = 1 To coll.count
     c = 1
        For r = 2 To .Cells(Rows.count, 1).End(xlUp).row
                If .Cells(r, 4) = coll.Item(i) Then
                    ReDim Preserve var(1 To 5, 1 To c)
                        var(1, c) = .Cells(r, 1)
                        var(2, c) = .Cells(r, 2)
                        var(3, c) = .Cells(r, 3)
                        var(4, c) = .Cells(r, 5)
                        var(5, c) = .Cells(r, 6)

                        count = count + 1
                        c = c + 1
                End If
        Next r
        
        'Add the variant containing the data to a dictionary. This is one order.
        Set dictSingle = New Dictionary
        dictSingle.Add coll.Item(i), var
        
        'Add every individual order to a master dictionary
        dictOrders.Add coll.Item(i), dictSingle
        
        'Erase the array
        Erase var
        
      Next i
  
  End With
  
End Sub