• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Nested collections in VBA

I am trying to work on nested collections but am unsuccessful.

Can someone show me how to assign the given data to a collection such that each row is a collection and these collections are assigned to a parent collection.

I have attached a workbook with the data and also the code which I have which does not work as desired.

It has to be Collections. Not dictionary or any alternative.
Nested-collection.xlsm
0
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 2
  • 2
1 Solution
 
Wayne Taylor (webtubbs)Commented:
I would create a new instance of 'drow' with each row iteration...

Sub collections()
    Dim data As New Collection
    Dim rw As Range
    Dim cel As Range    
    For Each rw In Range("A3:A8")        
        Dim drow As New Collection
        For Each cel In Range(rw, rw.End(xlToRight))
            drow.Add cel.Value
        Next cel
        data.Add drow        
    Next rw
End Sub

Open in new window

0
 
Wayne Taylor (webtubbs)Commented:
Sorry, should have tested first. Try this instead....

Sub collections()
    Dim data As New Collection
    Dim rw As Range
    Dim cel As Range    
    For Each rw In Range("A3:A8")
        Dim drow As New Collection
        For Each cel In Range(rw, rw.End(xlToRight))
            drow.Add cel.Value
        Next cel
        data.Add drow
        Set drow = Nothing
    Next rw
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerAuthor Commented:
Hi, when I run this code all items in data are the same from a to z
0
 
Saqib Husain, SyedEngineerAuthor Commented:
That works great.

Thanks

Saqib
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now