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.
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