Hi All,
I am having trouble with some code I've started writing.
I have two lists, both with references. I basically want to vlookup from one sheet (complete list) to the other sheet (incomplete list). Whatever isn't found in the incomplete list, add from the complete list.
Only add what's missing not everything! (What my code does - a combination of the missing and incomplete)
What I am getting is instead of the dictionary writing out just the new references I have to add to the incomplete list, it's adding both the elements together in the dictionary (what the final result will be).
I'll repeat it again, hard to explain I guess.
I don't want my dictionary to return the complete list, only the new items I need to add.
Hope that makes sense.
Code
Sub GetMissingCBSSIDs()
Dim i As Long
Dim AppsAmt As Double
Dim CBSSID As String
Dim Sh As Worksheet
Dim Sh2 As Worksheet
Dim RGMatches As Range
Dim Lastrow As Long
Dim IDBroker As Variant
Dim NewIDBroker As Variant
' Create dictonary
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
' Get worksheet
Set Sh = ThisWorkbook.Worksheets("Association Table")
Set Sh2 = ThisWorkbook.Sheets("Test")
' Vlookup Using Scripting Dictionary
Lastrow = Sh.Range("A" & Rows.Count).End(xlUp).Row
dict.RemoveAll ' clear the dictionary
' Looping to store values in dictionary
NewIDBroker = Sh.Range("a1").CurrentRegion.Value 'load our values from Test
For i = 2 To Lastrow
dict.Item(NewIDBroker(i, 1)) = NewIDBroker(i, 1)
Next i
'Looping to compare
IDBroker = Sh2.Range("a1").CurrentRegion.Value
Lastrow = Sh2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Lastrow
If dict.exists(IDBroker(i, 1)) Then ' we found the item
IDBroker(i, 1) = "not new"
Else:
IDBroker(i, 1) = dict.Item(IDBroker(i, 1)) ' not found
End If
Next i
'Dim key As Variant
'For Each key In dict.keys
' Debug.Print key
'Next key
'Print the values to sheet
Dim lrow As Long, k As Variant
lrow = 2
For Each IDBroker In dict.keys
Sheets("test").Cells(lrow, 4) = IDBroker
Sheets("test").Cells(lrow, 5) = dict(IDBroker)
lrow = lrow + 1
Next
End Sub
Display More