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

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.

HTH
Regards,
Zack Barresse
ASKER
leezac

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

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.
fblack61
ASKER CERTIFIED SOLUTION
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
or
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
ASKER
leezac

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

Very welcome, glad it works for you!

Zack