How to add error-checking for VLOOKUP in VBA

Question

I am moving some excel formula to visual basic to save some file space. One aspect I am struggling with is adding some error-checking.

I have a formula (in column 23/W): your text=IF(ISERROR(VLOOKUP(W2, auditdata!A:D, 1, FALSE)),"room not audited", "room audited")

That I have re-written as visual basic code, but I am struggling to add the "On Error" statement. I am using these instructions for reference: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/on-error-statement

Actually, the formula is a little more complicated - I need to check the values of two columns and if both exist in the Table Array I am checking, then return "audited" otherwise "not audited". I am simplifying the problem so I can better understand.

There will be two situations where an error can occur: if the cell checked is empty or it has a value that does not exist in the other data table. The VBA error is Run-time error '1004':

The code so far is:

Sub CheckRoomAudited_Method()
    Dim AuditCheckCell As Range 'cell where the vlookup results are returned

    Dim TotaltoCheck As Long 'last row in the column to check
    TotaltoCheck = Worksheets("RoomUse").Range("A2").End(xlDown).Row 'using first column as some data missing in lookup range column
    
    Dim EndofAuditRange As Long
    EndofAuditRange = Worksheets("auditdata").Range("A1").End(xlDown).Row 'this in the TableArray that will be checked
    
    Dim AuditCheckColumn As Range 'column where the vlookup results are returned
    Set AuditCheckColumn = Worksheets("RoomUse").Range(Worksheets("RoomUse").Cells(2, 23), Worksheets("RoomUse").Cells(TotaltoCheck, 23)) 'seemed long-winded to specify but could not get it to work otherwise     
    
    Dim VlookupValueColumn As Range 'need to check values in this column to see if they exist in other sheet
    Set VlookupValueColumn = Worksheets("RoomUse").Range(Worksheets("RoomUse").Cells(2, 37), Worksheets("RoomUse").Cells(TotaltoCheck, 37))
    
    Dim TableArrayRange As Range 'TableArray that will be checked
    Set TableArrayRange = Worksheets("auditdata").Range(Worksheets("auditdata").Cells(1, 1), Worksheets("auditdata").Cells(EndofAuditRange, 4))
    
    For Each AuditCheckCell In AuditCheckColumn
        'loop through column number 37 ("AK:AK") and check whether each value exist in the other table/sheet
    'write the value if it exist or "room not audited" if it does not exist in column 23/W
        AuditCheckCell.Value = WorksheetFunction.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
    Next AuditCheckCell
    
End Sub

Answer 1

There are basically two ways to execute VLookup from VBA: The first is the method you are using (WorksheetFunction.VLookup), the other is Application.VLookup.

Basically, they do the same, the only difference is the error-handling.

WorksheetFunction.VLookup throws a runtime error if a value cannot be found. You need to catch this error by yourself using the On Error-Statements:

Dim myValue As Variant
myValue = "(not found)"  ' Or whatever value you want to put into the cell if not found
On Error Resume Next    
myValue = WorksheetFunction.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
On Error Goto 0
AuditCheckCell.Value = myValue

Application.VLookup doesn't throw a runtime error. Instead, if the value is not found, it returns an error value. When you write that value into a cell, the cell will display #N/A - that's exactly what is returned from the VLookup-Function in an Excel formula. In VBA, you can check this using the functions IsError, or, more specifically, IsNA.

Dim myValue As Variant
myValue = Application.VLookup(AuditCheckCell.Offset(0, 14), TableArrayRange, 1, False)
if IsNA(myValue) Then
    AuditCheckCell.Value = "(not found)" 
Else
    AuditCheckCell.Value = myValue
End If