Dictionary Function Question...

[christianbiker]

Is it possbile using a loop or some other code to make a search using the dictionary function restricted to certain data within a spreadsheet? In the code below I have used the dictionary function and it works quite well with some of my spreadsheets however I want to use it with a large spreadsheet and pull data based on a certain criteria. In the range I have selected it is telling me the occurrence and how many times it has occurred which is great. I want to do the same thing however isolate certain the information returned based on a cruteria I specify. For instance, say I have 4 different types of data in column "A" (A, B, C, D) and I want to see all of the occurrences in the range selected but only from one of the 4 different data types that I specify, such a "B". This may not be a great explanation but hopefully someone can help me.

Thanks

Dim dic As Object, r As Range, rng As Range
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
Set rng = Sheets("UNIT DATA").Range("I2:AZ10000")
For Each r In rng
If Not IsEmpty(r) Then
If Not dic.exists(r.Value) Then
dic.Add r.Value, 1
Else
dic(r.Value) = dic(r.Value) + 1
End If
End If
Next
On Error GoTo nodata
Sheets("OCCURRENCE TOTALS").Range("a2").Resize(dic.Count) = _
Application.Transpose(dic.keys)
Sheets("OCCURRENCE TOTALS").Range("b2").Resize(dic.Count) = _
Application.Transpose(dic.items)
Set dic = Nothing

 

[jindon]

Not clear to me
try if this is what you are after...

Dim dic As Object, r As Range, rng As Range, e
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
For Each e In Array("A","B","C","D")
    dic.add e, 0
Next
Set rng = Sheets("UNIT DATA").Range("I2:AZ10000")
For Each r In rng
   If dic.exists(r.Value) Then dic(r.Value) = dic(r.Value) + 1
Next
Sheets("OCCURRENCE TOTALS").Range("a2").Resize(dic.Count) = _
Application.Transpose(dic.keys)
Sheets("OCCURRENCE TOTALS").Range("b2").Resize(dic.Count) = _
Application.Transpose(dic.items)
Set dic = Nothing

 

[pgc01]

Hi christianbiker

I see 3 solutions.

2 very easy to implement

- just loop through the keys and filter the ones you want

- create an array of 4 dictionaries, load the data accordingly. In this case, the type of report you want is very easy since the data is already separated.

Another solution, but I don't see the need in this case, would be to set up a multi-level dictionary, in this case with just 2 levels. The main dictionary would be the root node of a the tree of dictionaries and you could define types in the second level. As I said I don't think you need it here.

Hope this helps
PGC