Macro using Dictionary for first time not working

[Tim Hanson]

I am trying to use a Dictionary object for first time and am confused!

I am trying to compare columns on 2 different sheets then post back to a 3rd sheet
I get the proper number of matched rows but no matches( they are all "0")

I uploaded a workbook

In module2 I have a matching Macro "RemoveRowsByMatch" I used the generate the "Want" tab, I am trying to duplicate "RemoveRowsByMatch" using a Dictionary object because I think it is faster and I am trying to get an idea of how to use a Dictionary objects

Thanks for any help on this
Thank you

Sub FindDistinct()
Dim dict As Object
Dim arr As Variant
Dim wsS As Worksheet, wsFind As Worksheet, wsPB As Worksheet
Dim Counter As Long

  
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
  
  Set dict = CreateObject("Scripting.Dictionary")
    Set wsS = ThisWorkbook.Sheets("XXX")
Set wsFind = ThisWorkbook.Sheets("YYY")
  Set wsPB = ThisWorkbook.Sheets("ZZZ")

      arrS = wsS.Range("A2", wsS.Cells(wsS.Rows.Count, "A")).Value
    arrFind = wsFind.Range("A2", wsFind.Cells(wsFind.Rows.Count, "A")).Value
  
    With wsS
        Set dict = CreateObject("Scripting.Dictionary")
        dict.CompareMode = vbTextCompare
        On Error Resume Next
        For Counter = 1 To UBound(arrS, 1)
            If Not dict.Exists(arrS(Counter, 1)) Then dict.Add arrS(Counter, 1), arrFind(Counter, 1)
        Next
        On Error GoTo 0
    End With
  
      With wsPB
        .Range("A1") = "Dictionary"
        arrS = dict.Items
        .Range("A2").Resize(dict.Count, 1).Value = Application.Transpose(arr)
        Set dic = Nothing
      
        ' Resize columns as needed
        .Columns.AutoFit
    End With
  
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
  
    MsgBox "Done"
  
End Sub

 

[NARAYANK991]

Hi Tim ,

As a first step , have you checked out this article , which is almost the defacto goto for this topic ?

http://www.experts-exchange.com/Sof...A_3391-Using-the-Dictionary-Class-in-VBA.html

Narayan

 

[Tim Hanson]

Hello Narayan, that is where I got the basic code I am using from. Alas the part I need

Hierarchy of Distinct Values Across Two Columns ("Dictionary of Dictionaries")

you need to pay for and I might have to do that but I will continue to rearch for more information elsewhere first.

Thanks for the comment I much appriciated.

 

[NARAYANK991]

Hi Tim ,

If you can explain what exactly you want to do , it might make it easier for others to suggest more helpful answers ; I am not able to understand why you would want to use the Dictionary of Dictionaries construct.

Narayan

 

[Tim Hanson]

Hello Narayan, because from the link I thought that was what one had to do, I just am trying to learn how to match collumns on two different sheets using the Dictionary object method

 

[NARAYANK991]

Hi Tim ,

I have not used dictionaries much ; I can go through your code and correct it where ever necessary ; however , I think Marc and Shrivallabha are our resident experts on dictionaries , since they have used them quite a lot in their answers ; in case you don't get a response from either of them today or tomorrow , I'll reply.

From what I can understand , the code should go through one column and store its contents in a dictionary ; when it goes through the contents of the second column , for every item in the column , it can query the dictionary using the Exists method of the Dictionary class ; if this returns true , then it is a match.

I don't think you need to use the Dictionary of Dictionaries construct.

Narayan

 

[Tim Hanson]

Hello Narayan, thank you for your responce, I will contine to search for more information

 

[Marc L]

Hi Tim !

'cause Dictionary object is very common, you can read how to use it
directly in VBA inner help by entering Dictionary in the VBA help label !

Or see in MSDN web site …

Nothing difficult, beginner level …

If there is no much lines to compare,
worksheet functions MATCH (& INDEX) can do easily the job !

Many samples in this forum, whatever for this object or these functions …
​

 

[Smallman]

Tim

Would have been a lot easer rather than pointing us to a macro RemoveRowsByMatch to just say what you are trying to do?

For example, I want to make a list of the matching items between sheet 1 and sheet2 on sheet 3. You would have an answer already.

I am not even sure the Want sheet is correct. I can see nothing from the YYY tab in there.

What is it that you are trying to achieve?
Smallman