VBA Excel - If Found in List, Then Omit

Question

I'm trying to write a script that compares values in one list to values in another. If the value is not found in the comparison list, I would like to copy the whole row from one worksheet to another.

Using Application.VLookup/Application.WorksheetFunction.Vlookup, I'm getting a debug error 13/1004 because no value is found. I'd like to trigger off of that no value found to make the copy.

Function Update()

Dim Master As Worksheet
Dim Slave As Worksheet
Dim lrM As Long
Dim lrS As Long
Dim i As Long, m, MLookup As Range
'Dim j As Long, n, SLookup As Range

Set Master = ThisWorkbook.Worksheets("PR Data Windchill")
Set Slave = ThisWorkbook.Worksheets("PR Data")

Set MLookup = ThisWorkbook.Worksheets("PR Data").Columns(1)
'Set SLookup = ThisWorkbook.Worksheets("PR Data Windchill").Columns(7)

lrM = Master.Cells(Master.Rows.Count, "A").End(xlUp).Row
lrS = Slave.Cells(Slave.Rows.Count, "A").End(xlUp).Row

With Master
    For i = 2 To lrM
        m = Application.Match(.Rows(i).Cells(1).Value, MLookup, 0)
        If IsError(m) Then
            .Rows(i).Copy Slave.Cells(Slave.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next i
End With

'With Slave
'    For j = 2 To lrS
'        n = Application.Match(.Rows(j).Cells(1).Value, SLookup, 0)
'        If IsError(n) Then
'            .Rows(j).Copy Slave.Cells(Slave.Rows.Count, "A").End(xlUp).Offset(1, 0)
'        End If
'    Next j
'End With

Application.CutCopyMode = False

MsgBox ("Matrix Update Complete")

End Function

Try setting a variable of type `Range` equal to `Range.Find` then test `If RangeVariable Is Nothing Then`.... — Brian, May 21 '19 at 17:13
If you are looking to keep to your current use of a vlookup, you can wrap it in `If IsError() Then` and do the copy. The end result is similar to @Brian 's, where a check is performed. `IsError` will return boolean (true/false) — Cyril, May 21 '19 at 17:24
First, [avoid using `.SELECT` and `.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) it's rarely necessary and this isn't one of those cases. Second, I'm confused - I don't see any reference to `.Vlookup` in the code you posted. — FreeMan, May 21 '19 at 18:06

Tim Williams · Accepted Answer · 2019-05-22T18:35:26.717

0

This is my best guess (including second lookup):

Sub Update()

    Dim Master As Worksheet
    Dim Slave As Worksheet
    Dim lrM As Long
    Dim lrS As Long
    Dim i As Long, m, MLookup As Range, SLookup As Range

    Set Master = ThisWorkbook.Worksheets("PR Data Windchill")
    Set Slave = ThisWorkbook.Worksheets("PR Data")

    Set MLookup = Slave.Columns(1)  '<< reference list
    Set SLookup = Master.Columns(1) '<< reference list

    lrM = Master.Cells(Master.Rows.Count, "A").End(xlUp).Row
    lrS = Slave.Cells(Slave.Rows.Count, "A").End(xlUp).Row

    With Master
        For i = 2 To lrM
            'drop the worksheetfunction to avoid a run-time error,
            '   and instead check the return value for an error
            m = Application.Match(.Rows(i).Cells(1).Value, MLookup, 0)
            If IsError(m) Then
                'no match, so copy over
                .Rows(i).Copy Slave.Cells(Slave.Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next i
    End With

    With Slave
        For i = 2 To lrS
            m = Application.Match(.Rows(i).Cells(1).Value, SLookup, 0)
            If Not IsError(m) Then
                Master.Cells(m, 7).Copy S.Rows(i).Cells(1)
            End If
        Next j
    End With

    Application.CutCopyMode = False

    MsgBox "Matrix Update Complete"

End Function

edited May 22 '19 at 18:35

answered May 21 '19 at 18:30

Tim Williams

154,628
8
97
125

This worked! I need help expanding the function, though. I've updated the original code to reflect what's changed. Using the "PR Data Windchill" tab data, I need to update a field 7th to the right in "PR Data". What have I shifted wrong? – benwalden May 21 '19 at 21:23
Would help to explain what's changed. Also - it's best not to alter your question's original code, otherwise your whole question starts to make no sense to anyone who comes along later. – Tim Williams May 21 '19 at 21:26
I added a new With Slave section that wasn't there previously. The intent is to review and update a cell instead of copying a whole row. – benwalden May 21 '19 at 21:28
Exactly what do you want to copy from where to where? – Tim Williams May 21 '19 at 21:30
On Slave, Copy from PR Data Windchill, 7th column per matched row and paste it onto the matching row 7th column of PR Data per matched row. – benwalden May 21 '19 at 21:33
Can you help me with this question? I tried everything I knew but could not solve. https://stackoverflow.com/q/56207459/8631622 @Tim Williams – Pie May 22 '19 at 07:29

VBA Excel - If Found in List, Then Omit

1 Answers1