Dictionary - defining range

[filido]

Hi

I would like to add a list of items to a dictionary and then loop each item. The problem is, I don't know how to refer to a list range that is on another worksheet than the main data that is being filtered. "Items" are located on sheet wsList, so where am I suppose to add it?


Dim dict As Object, Items As Variant, c As Long, item As Variant
Set dict = CreateObject("Scripting.Dictionary")

With wsData


Dim ui As Double

If Range("D3").Value <> "" Then
ui = 2
Items = Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp)) --> Items = Range(wsList.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))doesn't work


For c = 1 To UBound(Items, 1)
dict(Items(c, 1)) = 1
Next

Else

item = Range("A2").Value --> ​same problem
ui = 1
End If

End with


The above is just a small part of my code, please let me know if you need to see more of it.
Many thanks!

 

[mohadin]

Hi,
The method is
[FONT=&quot]dict.[/FONT][FONT=&quot]Add[/FONT][FONT=&quot] [/FONT]key[FONT=&quot], [/FONT]item

 

[JackDanIce]

I think this is what you need:

Sub M2()

    Dim d   As Object: Set d = CreateObject("Scripting.Dictionary")
    Dim v   As Variant
    Dim x   As Long
    Dim i   As Long: i = 1
    
    With wsList
        x = Cells(.Rows.Count, 1).End(xlUp).Row - 1
        If Len(wsData.Cells(3, 4).Value) = 0 Then x = 1
        v = .Cells(2, 1).Resize(x).Value
    End With
    
    If x > 1 Then
        i = 2
        For x = LBound(v, 1) To UBound(v, 1)
            d(v(x, 1)) = 1
        Next x
    End If
            
End Sub