Add item to collection with key or before/after
Results 1 to 4 of 4

Thread: Add item to collection with key or before/after
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular paipimenta's Avatar
    Join Date
    Apr 2010
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add item to collection with key or before/after

    I'm trying to add values in a range to a collection. here's the range:
    .............A
    1 Product
    2 Pricing
    3 Environment
    4 Customer Service


    Here's my code:
    Code:
    Sub loadCollection()
        Dim mCats As New Collection
        Dim cl, part As Variant
        For Each cl In Range("A1:A4")
            mCats.Add(cl.Value)
        Next cl
        For Each part In mCats
            MsgBox part
        Next part
    End Sub
    This works fine, however, when I change the mCats.Add line to the following:
    Code:
    mCats.Add(cl.Value,,1)
    in order to insert them in reverse order, it says "Expected: =" same thing if I put in a key or an after value. Compile error, not run-time. What's up?
    I am using Excel 2003 at work, unless explicitly specified otherwise in the thread.

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,519
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Add item to collection with key or before/after

    This works, in order to add before or after an item in the collection, that item needs to exist.

    Code:
    Sub loadCollection()
        Dim mCats As New Collection
        Dim cl, part As Variant
        mCats.Add Item:="x", Key:="x"
        For Each cl In Range("A1:A4")
            mCats.Add Item:=cl.Value, Key:=cl.Value, after:=1
        Next cl
        mCats.Remove 1
        For part = 1 To mCats.Count
            MsgBox mCats(part)
        Next part
    End Sub

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add item to collection with key or before/after

    How about replacing For Each with For.
    Code:
    Sub loadCollection()
        Dim mCats As New Collection
        Dim cl, part As Variant
        Dim i as Long
        'For Each cl In Range("A1:A4")
        For i = Range("A1:A4").Cells.Count To 1 Step -1
            Set cl = Range("A1:A4").Cells(i)
            mCats.Add (cl.Value)
        Next
        For Each part In mCats
            MsgBox part
        Next part
    End Sub

  4. #4
    Board Regular paipimenta's Avatar
    Join Date
    Apr 2010
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add item to collection with key or before/after

    Interesting approach, Akihito. You've helped me think outside of my box a little more today.
    I am using Excel 2003 at work, unless explicitly specified otherwise in the thread.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •