CODEWITHSUNDEEP
vbacollections
Tawm
Tawm

Reputation: 545

VBA: Dim to set a Collection to

I'm trying to use a collection of Filenames to loop through each item in that collection.

I can't figure out how to assign the collection to a Dim so that I can call an item from that collection easily.

What I've got:

Sub VlookupMGCCode(ByRef reports, ByRef item1, ByRef item2, ByRef item3, ByRef item4)

Dim lastrow As Integer
Dim wRange As Range
Dim blankRange As Range
Dim temp As Object

lastrow = Cells(Rows.count, "A").End(xlUp).Row
Set wRange = Range("$T$7:$T$" & lastrow)

temp = CollectReports(item1, item2, item3, item4)

For x = 1 To 3
    Set blankRange = wRange.SpecialCells(xlCellTypeBlanks)
    blankRange.FindNext("").FormulaR1C1 = "=VLOOKUP(RC[-18],'[" & temp.Item(x) & "]Sheet1'!C1:C31,31,FALSE)"

    With blankRange
        .FillDown
        .Copy
        .PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False
    End With
Next

End Sub

Function CollectReports(ByRef item1, ByRef item2, ByRef item3, ByRef item4) As Collection

Dim reports As New Collection

reports.Add Item:=item1, Key:="1"
reports.Add Item:=item2, Key:="2"
reports.Add Item:=item3, Key:="3"
reports.Add Item:=item4, Key:="4"

Set CollectReports = reports

End Function

In case it's confusing, I'm only running from x= 1 To 3 because only the first three items in the collection apply to the report being run right now. The last applies to a different similar report which is collection will also be used for.

As you can see, I'm trying to classify the Collection as an Object, and therefore get a Run-Time error of Object variable or With block variable not set.

Could you tell me how I could go about doing this?

Thank you!

Upvotes: 0

Views: 159

Answers (1)

NYCdotNet
NYCdotNet

Reputation: 4647

I think you may be doing it correctly. The issue is that you're not using set when assigning temp.

Set temp = CollectReports(item1, item2, item3, item4)

You can also define temp as a Collection, but Object should probably be fine.

Upvotes: 2

Related Questions