Hi All,
I'm new to VBA scripting dictionary and I'm close to solving this.
Basically I have one list on one sheet, and I want to compare it to another and count how many instances of the number occur (countif).
This is what I have so far.
Code
Sub DictionaryCountifs()
Dim i As Long
Dim Lastrow As Long
Dim Lastrow2 As Long
Dim TheArray() As Variant
Dim ERPIDnDPC As String
Dim Dict As Object
Dim Sh As Worksheet
Dim Sh2 As Worksheet
Dim Key As Variant
Dim SumIt As Long
Set Dict = CreateObject("Scripting.Dictionary")
Set Sh = ThisWorkbook.Sheets("Sales Calculation")
'Countif
Sh.Activate ' sheet must be activated
ReDim TheArray(i) 'clear TheArray
Lastrow = Sh.Range("A" & Rows.Count).End(xlUp).Row
TheArray = Sh.Range("A2:A" & Lastrow).Value 'what we are loading
'Check what we loaded in a loop
'For i = 1 To UBound(TheArray) ' loop between to the upper bound of the array
'
' Debug.Print TheArray(i, 1) 'check items loaded in the array
'
'Next i
Dict.RemoveAll
'Load Values into Dictionary
For i = 1 To UBound(TheArray)
'item 'key
Dict.Item(TheArray(i, 1)) = TheArray(i, 1)
Next i
' Loop through the dictionary
For Each Key In Dict
Debug.Print Key, Dict(Key)
Next Key
' Compare to Another List
Lastrow = Sh.Range("Q" & Rows.Count).End(xlUp).Row
TheArray = Sh.Range("Q1:Q" & Lastrow).Value 'what we are loading
' Loop to check
For i = 1 To UBound(TheArray)
Debug.Print TheArray(i, 1)
If Dict.Exists(TheArray(i, 1)) Then 'if they key exists then
MsgBox ("Yes")
Dict.Item(TheArray(i, 1)) = SumIt + 1
End If
Next i
' Loop through the dictionary
For Each Key In Dict
Debug.Print Key, Dict(Key)
Next Key
Display More