?
Solved

Nested collections in VBA

Posted on 2015-02-11
4
Medium Priority
?
174 Views
Last Modified: 2016-02-10
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
Comment
Question by:Saqib Husain, Syed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40604412
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
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 2000 total points
ID: 40604415
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
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 40604422
Hi, when I run this code all items in data are the same from a to z
0
 
LVL 43

Author Closing Comment

by:Saqib Husain, Syed
ID: 40604430
That works great.

Thanks

Saqib
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question