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
leezacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zack BarresseCEOCommented:
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
0
leezacAuthor Commented:
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.
0
leezacAuthor Commented:
Well I probably need the code I took out it seems, not sure how to fix though.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Zack BarresseCEOCommented:
It worked for me. Hmm. I wouldn't take any of the code out. Instead I would add a line between these two...
iCheck = WorksheetFunction.Match(rCell.Value, wsMemory.Range("D:D"), 0)
GoTo MatchFound

Open in new window

Add this line of code between the two above lines...
If iCheck = 0 Then GoTo NoMatch

Open in new window

Or, you could always just do a somewhat sloppy error handling, which would make the routine look like this...

    On Error Resume Next
    For Each rCell In rCheck.Cells
        iCheck = 0
        iCheck = WorksheetFunction.Match(rCell.Value, wsMemory.Range("D:D"), 0)
        If iCheck = 0 Then
            wsMemory.Rows(4).Copy
            wsMemory.Rows(4).Insert
            wsMemory.Range("A4:C4").ClearContents
            wsMemory.Range("D4").Value = rCell.Value
            iCnt = iCnt + 1
        End If
    Next rCell
    On Error GoTo 0

Open in new window


Zack
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leezacAuthor Commented:
I added the last On Error Resume and it seems to work... Thanks...
0
Zack BarresseCEOCommented:
Very welcome, glad it works for you!

Zack
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.