Unique values using Dictionary with criteria

[leterrier]

I have a spreadsheet with 3 columns. Column A contains product type names with the range named as "Product".Column B is a description of the product names and Column C contains supplier names with the range named as "supplier".


I'm using a macro based on a Dictionary object to extract a list of unique supplier names from column C but now wish to modify it so that I can extract a unique list of suppliers based on the Product type. In other words, if product type is "some product", show me a list of its unique suppliers. A sample of the code is below. It's based on a reply to a related question on the Stackoverflow forum by user Issun (thanks).


I hope I've explained clearly. Thank you in advance for your help!

Sub UniqueRep()


Dim dict1 As Object
Set dict1 = CreateObject("scripting.dictionary")
Dim var1 As Variant, element As Variant


var1 = Range("supplier").Value


'Generate unique list and count occurrences


For Each element In var1
    If dict.1exists(element) Then
        dict1.Item(element) = dict1.Item(element) + 1
    Else
        dict1.Add element, 1
    End If
Next


'Paste report data
Sheet2.Range("A1").Resize(dict1.Count, 1).Value = _
    WorksheetFunction.Transpose(dict1.keys)
Sheet2.Range("B1").Resize(dict1.Count, 1).Value = _
    WorksheetFunction.Transpose(dict1.items)


End Sub

 

[Norie]

Is it actually this line that causes the error?

strProduct = Range("G1").Value

What's in G1?

 

[leterrier]

Norie,

Thanks, that line is the problem. G1 holds a text value eg "Paper"(without the quotes).

strProduct = "Paper"

works but

strProduct = Range("G1").value

or

strProduct = Range("G1")

doesn't. Hope this helps. Thank you again.

 

[Norie]

The only reason I can see for a type mismatch would be if G1 contained an error value.

If it contained a number it wouldn't be a problem, though the number would be converted to a string.

Could you upload a sample workbook to a file-sharing site like Box.net?

 

[leterrier]

Norie,

Thank you, I wll upload a sample shortly, regards.

 

[leterrier]

Norie,

Here is my sample file. I am much obliged for your continued interest.

https://drive.google.com/file/d/0Bxou7XlvWV3RZmgzZHFwaWN5UkE/edit?usp=sharing

 

[Norie]

The code works fine for me when I use a range for strProduct.

 

[leterrier]

Norie,

Thank you once again. I really don't understand it. I know it should work but at this end it simply won't. I won't take up anymore of your time, I will have to come up with something else. I appreciate all your help. Thanks.

 

[leterrier]

Norie, I gave it one more go and it worked!!! I had the dict variable set to be case sensitive. Problem solved. Hurrah!!! Thanks again!

 

[Norie]

Glad it's working but I'm a little confused.

How could a setting for the dictionary object affect code that assigns the value from a cell to a string variable?

 

[leterrier]

Norie, thanks - I have no idea. Once I ensured the range value had the same case as the entries in the named range it worked perfectly. I tested it and couldn't replicate the error when the correct case was observed. My knowledge of why things happen is limited and is the reason I seek the assistance of learned people like yourself. If anything should come to light to explain, I will post again to share with you. Thanks!