VBA Excel – IFERROR & VLOOKUP error

by

Using Application.WorksheetFunction.VLookup( will break the code if the value is not found.

Wrap it in vba error control.

Function CCC(A As Range)
Dim B As Variant
B = -1
On Error Resume Next
B = Application.WorksheetFunction.VLookup(A.Value, Sheets("DAP").Range("$B$4:$X$7"), 5, False)
On error goto 0
CCC = B

End Function

Another method is to Late Bind by removing the .WorksheetFormula and then testing the result.

Function CCC(A As Range)
Dim B As Variant
B = Application.VLookup(A.Value, Sheets("DAP").Range("$B$4:$X$7"), 5, False)
If IsError(B) Then
    CCC = -1
Else
    CCC = B
End If
End Function

More Related Contents:

  1. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  2. filter out multiple criteria using excel vba
  3. How to use workbook.saveas with automatic Overwrite
  4. Cannot VBA write data to cells in Excel 2007/2010 within a function
  5. Excel error 1004 “Unable to get …. property of WorksheetFunction class” appearing inconsistently
  6. Find last row in range
  7. Excel: the Incredible Shrinking and Expanding Controls [closed]
  8. Excel VBA Run-time error ’13’ Type mismatch
  9. How does one restore default case to a variable in VBA (Excel 2010)?
  10. Excel VBA Run-time Error ‘32809’ – Trying to Understand it
  11. How to get MS Excel to list files in a folder with their metadata (in particular, the tags created for the files)?
  12. Excel – opening file stuck on 100%, until VBA code finishes
  13. How to add a reference programmatically using VBA
  14. automatically execute an Excel macro on a cell change
  15. VLOOKUP with multiple criteria returning values in one cell
  16. VBA check if file exists
  17. How can I concatenate strings in VBA?
  18. Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
  19. Trying to use variables as values vba to sql
  20. How to call a macro from a button and pass arguments
  21. VBA (Excel) Dictionary on Mac?
  22. Paste Excel range in Outlook
  23. Refresh Excel VBA Function Results
  24. excel VBA run macro automatically whenever a cell is changed
  25. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  26. VBa conditional delete loop not working
  27. Concatenate column headers if value in rows below is non-blank
  28. Update excel sheet based on outlook mail [closed]
  29. Error13 in Excel VBA in data file
  30. How to highlight selected text within excel

Leave a Comment