Formula or VBA to find unmatched cell

Hi there,

If I understood you right, perhaps this is what you want..

Sub CopyUnmatchedHostNames()
    
    Dim wsMemoryImport As Worksheet
    Dim wsMemory As Worksheet
    Dim rCheck As Range
    Dim rCell As Range
    Dim iCheck As Long
    Dim iCnt As Long
    
    Set wsMemoryImport = ThisWorkbook.Worksheets("MemoryImport")
    Set wsMemory = ThisWorkbook.Worksheets("Memory")
    With wsMemoryImport
        Set rCheck = .Range("A4", .Cells(.Rows.Count, 1).End(xlUp))
    End With
    For Each rCell In rCheck.Cells
        On Error GoTo NoMatch
        iCheck = 0
        iCheck = WorksheetFunction.Match(rCell.Value, wsMemory.Range("D:D"), 0)
        GoTo MatchFound
NoMatch:
        On Error Resume Next
        wsMemory.Rows(4).Copy
        wsMemory.Rows(4).Insert
        wsMemory.Range("A4:C4").ClearContents
        wsMemory.Range("D4").Value = rCell.Value
        iCnt = iCnt + 1
MatchFound:
        On Error GoTo 0
    Next rCell
    
    If iCnt > 0 Then
        MsgBox iCnt & " total record(s) were copied.", vbExclamation, "DONE!"
    Else
        MsgBox "No Host Names copied.", vbExclamation, "DONE!"
    End If
    
End Sub

Select allOpen in new window

This will go through the MemoryImport sheet looking at the Host Name (col A) looking for matches in the Memory sheet in column D (Host Name). If any values are not found in the Memory sheet it will insert a row at row 4, clear cols A through C of that row and populate D4 with the Host Name. There is a message box telling you how many were copied, if at all.

HTH
Regards,
Zack Barresse

Looks exactly what I need, but if there is a second mismatch on Memory Import - I get an

error on this line

iCheck = WorksheetFunction.Match(rCell.Value, wsMemory.Range("D:D"), 0)

I took out

iCheck = WorksheetFunction.Match(rCell.Value, wsMemory.Range("D:D"), 0)
GoTo MatchFound

since I do not need to find matches and it works good now without error and copies the values I need to the memory tab.

Well I probably need the code I took out it seems, not sure how to fix though.

I added the last On Error Resume and it seems to work... Thanks...

Very welcome, glad it works for you!

Zack