Add item to collection with key or before/after

[paipimenta]

I'm trying to add values in a range to a collection. here's the range:
<TABLE style="WIDTH: 103pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=137 x:str><COLGROUP><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 103pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=137>.............A
1 Product
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>2 Pricing</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>3 Environment</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>4 Customer Service</TD></TR></TBODY></TABLE>

Here's my 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:

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?

 

[mikerickson]

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

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

 

[Akihito Yamashiro]

How about replacing For Each with For.

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

 

[paipimenta]

Interesting approach, Akihito. You've helped me think outside of my box a little more today.