Calling a Dictionary Value

[SLogue]

Hi All,

I'm having some troubles calling the value in a dictionary, I assume it's a simple syntax issue so hopefully someone will be able to educate me easily enough.

I'm also not 100% sure that a dictionary is the best/easiest way to go about doing what I'm doing, so I'm open to alternative options.

So what I'm trying to do is build a database with a list of parts measured, and work out how many are built into assemblies and how many parts remain unused. The problem is I can sometimes use the same part into multiple assemblies, but it's still only using 1 measured part from stock.

So the first thing I do is work out all the unique numbers that are built into modules, say for example it's [101 103 101 104]. I use a dictionary to list all the unique values [101 103 104].

Then lets say I've got 5 measured parts in stock, [101 102 103 104 105]. What I was trying to do was create a loop that went through each of the parts built (in the dictionary) and compare to the measured list.

 

[Fluff]

Hia
Is this the sort of thing you're after

    Set ShtDict = CreateObject("scripting.dictionary")
    With ShtDict
        For Each Sht In Sheets
            If Not .exists(Sht.Name) Then .Add Sht.Name, Nothing
        Next Sht
        
            i = 1
        For Each ky In .keys
            Sheets(ky).Tab.ColorIndex = i
            i = i + 1
        Next ky
    End With