How to prevent data loss using Application.VLookup?

The following macro utilizes the VLookup function between two workbooks. Both workbooks contain tables. The intent is for book1 to update values from book2

The data loss (cells that have values in book1, column 7 gets removed) occurs whenever the lookup value is not present in book2. Which leads to my question: how to prevent this deletion from occurring?

Sub Update()
Dim lookFor As Range
Dim srchRange As Range

Dim book1 As Workbook
Dim book2 As Workbook

Dim book2Name As String
book2Name = "table2.xlsm"

Dim book2NamePath As String
book2NamePath = ThisWorkbook.Path & "\" & book2Name

Set book1 = ThisWorkbook

If IsOpen(book2Name) = False Then Workbooks.Open (book2NamePath)
Set book2 = Workbooks(book2Name)

Set lookFor = book1.Sheets(1).Range("a23:a100")
Set srchRange = book2.Sheets(1).Range("b:f")

lookFor.Offset(0, 7).Value = Application.VLookup(lookFor, srchRange, 2, False)

End Sub

1 answer

  • answered 2019-03-13 18:08 user11198948

    Test the return value and only update if there is something to return.

    Dim val as Variant
    Set lookFor = book1.Sheets(1).Range("a23:a100")
    Set srchRange = book2.Sheets(1).Range("b:f")
    val = Application.VLookup(lookFor.Cells(1), srchRange, 2, False)
    If Not IsError(val) Then
        lookFor.Offset(0, 7).Value = val
    End If

    You can test the return to a variant with Application.VLookup or Application.Match. It has to be returned to a variant and it doesn't work with WorksheetFunction.VLookup or WorksheetFunction.Match.