Avatar of leezac
leezac asked on

Formula or VBA to find unmatched cell

In the attached example - I need to be able to do the following:

If Name on MemoryImport sheet does not match Name on Memory sheet then copy the unmatched Name from the MemoryImport sheet to the Memory sheet inserting row or rows starting at row 4 and inserting the unmatched Name into column A.  The other rows on the Memory tab have formulas and will need to retain the cell references for those also.

Thanks in advance.
Microsoft Excel

Avatar of undefined
Last Comment
Zack Barresse

8/22/2022 - Mon
Zack Barresse

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
        On Error Resume Next
        wsMemory.Range("D4").Value = rCell.Value
        iCnt = iCnt + 1
        On Error GoTo 0
    Next rCell
    If iCnt > 0 Then
        MsgBox iCnt & " total record(s) were copied.", vbExclamation, "DONE!"
        MsgBox "No Host Names copied.", vbExclamation, "DONE!"
    End If
End Sub

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

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.
Your help has saved me hundreds of hours of internet surfing.
Zack Barresse

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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

Very welcome, glad it works for you!