0

I'm trying to compare keywords from sheet 1 to sheet 2 and return keywords that do not have a match. I'm at a loss as to what my problem is here.

Sub CompareKeywords()
    
    Dim keyword As Variant
    Dim matchFound As Boolean
    
    'Loop through each keyword in sheet 1
    For Each keyword In Sheets("Sheet1").Range("A:A")
    
        'Search for the keyword in sheet 2
        matchFound = False
        For Each cell In Sheets("Sheet2").Range("A:A")
            If keyword = cell.value Then
                matchFound = True
                Exit For
            End If
        Next cell
    
        'If the keyword was not found in sheet 2, add it to the list of keywords without matches
        If Not matchFound Then
            Dim lastUsedCell As Range
            Set lastUsedCell = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "B").End(xlDown)
            lastUsedCell.Offset(1, 0).value = keyword
        End If
    
    Next keyword
    
    End Sub

I'm getting a runtime error 1004 Application Defined or Object Defined error

What am I missing?

Jakebnda
  • 37
  • 5
  • 1
    `Set lastUsedCell = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp)` – BigBen Dec 06 '22 at 17:18
  • Much more efficient to use `Find` or `Match` and one loop, instead of two loops. – BigBen Dec 06 '22 at 17:22
  • 1
    `.Rows.Count` in column B takes you to the very last cell in that column - `.End(xlDown)` is no-op because you're already at the bottom of the sheet... and then `.Offset(1,0)` attempts to grab a cell _beyond_ the worksheet, hence error 1004. Use `.End(xlUp)` as @BigBen said, to get to the last cell with data instead. – Mathieu Guindon Dec 06 '22 at 17:47

0 Answers0