VBA Error Handling For Vlookup

Question

I developed the code for a vlookup but I'm having trouble with error handling. The values of a column on Sheet1 are being looked up in a column on Sheet2. The results are displayed on Sheet3.

There are 2 instances where there would be errors:

If cells in the lookup value column are blank
If the lookup values aren't in the table array

If the cells of the lookup values are blank, I want the results to display blanks. If the lookup values aren't blank but are missing from the table array, I want the cells to display "Missing". Right now the code is set up to just always give blanks.

The code I have so far is below. I'm an excel formula pro and a VBA novice. Any help would be appreciated!

On Error Resume Next

Dim Fund_Row As Long
Dim Fund_Clm As Long

Table1 = rangeA  'Column on Sheet1
Table2 = rangeB  'Column on Sheet2

Fund_Row = Sheets("Sheet3").Range("B2").Row
Fund_Clm = Sheets("Sheet3").Range("B2").Column

For Each cl In Table1
Sheets("Sheet3").Cells(Fund_Row, Fund_Clm)=    

Application.WorksheetFunction.VLookup(cl, Table2, 1, False)
Fund_Row = Fund_Row + 1
Next cl

End Sub

I can't see how this requires VBA at all, as opposed to using VLOOKUP as a worksheet function like it was intended. (Here is Microsoft's tutorial: Vlookup: When and How to use it) — ashleedawg

MarcinSzaleniec MarcinSzaleniec · Accepted Answer · 2018-01-08T08:33:16

For catching #NA! error, you could use just IFNA worksheet function. Just like that:

With Application.WorksheetFunction
    Sheets("Sheet3").Cells(Fund_Row, Fund_Clm) = .IfNa(.VLookup(cl, Table2, 1, False), "Missing")
End With

You can deal with blanks two way. The simple is to add blanks to the table you are vlookuping. Little more sophisticated is add another if in code:

If cl.value = "" then ...

VBA Error Handling For Vlookup

1 Answers