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