[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Nested collections in VBA

Posted on 2015-02-11
4
Medium Priority
?
201 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 48

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 48

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

656 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