I have three worksheets with consistent headers, but the # of columns vary:

1. Active List
2. Current List
3. New List

I need to compare column A from "New List" against Column B from "Active List" for duplicate instances. I want to load only the unique instances from column A starting at row 2 along with the associated cells in Column B on my "New List" beneath the last, populated row of my "Active List" worksheet in columns B & C.

To do this, I have tried utilizing the Scripting Dictionary, but I receive Run Time Error 1004 on my object range in the following line of code:

Dict.Add Key:=NL.Range(i, "A").Value, Item:=vbNullString

Here is the full code which I mimicked from question #55499372 on StackOverflow:

Sub load_new()

 Dim LastRow As Long
 Dim i As Long
 
 Dim Dict As Scripting.Dictionary
 Set Dict = New Scripting.Dictionary
 Dim CL As Worksheet
 Set CL = ThisWorkbook.Worksheets("CURRENT LIST")
 Dim NL As Worksheet
 Set NL = ThisWorkbook.Worksheets("NEW LIST")
 Dim AL As Worksheet
 Set AL = ThisWorkbook.Worksheets("ACTIVE LIST")

    'Retrieves the last row of column A
    With NL
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
 
    For i = 2 To LastRow
        Dict.Add Key:=NL.Range(i, 1).Value, Item:=vbNullString
    Next i
    

    'Retrieves the last row of column B
    With AL
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    End With

    For i = 2 To LastRow
        If Not Dict.Exists(AL.Range(i, 2).Value) Then
        End If
    Next i

End Sub

CodePudding user response：

Load the dictionary with keys from Active List column B, then scan the New List column A checking if key does not exist.

Option Explicit

Sub load_new()

    Dim wsCL As Worksheet, wsNL As Worksheet, wsAL As Worksheet
    Dim LastRowAL As Long, LastRowNL As Long
    Dim i As Long, n As Long, key As String
    
    Dim Dict As Scripting.dictionary
    Set Dict = New Scripting.dictionary
    With ThisWorkbook
        'Set wsCL = .Sheets("CURRENT LIST")
        Set wsNL = .Sheets("NEW LIST")
        Set wsAL = .Sheets("ACTIVE LIST")
    End With

    ' Active List
    With wsAL
        LastRowAL = .Cells(.Rows.Count, "B").End(xlUp).Row
        For i = 2 To LastRowAL
            key = Trim(.Cells(i, "B"))
            If Len(key) > 0 Then
                Dict.Add key, i
            End If
        Next i
    End With
 
    ' New List
    With wsNL
        LastRowNL = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To LastRowNL
            key = Trim(.Cells(i, "A"))
            If Not Dict.Exists(key) Then
                LastRowAL = LastRowAL   1
                wsAL.Cells(LastRowAL, "B") = key
                wsAL.Cells(LastRowAL, "C") = .Cells(i, "B")
               n = n   1
            End If
        Next i
    End With
    MsgBox n & " rows added to " & wsAL.Name

End Sub