Calling IfError with vLookUp in VBA returns error even though cell formula returns value

0
votes

I'm using ifError coupled with vLookUp to look for values in a large table and pull out a value from a specific column to be placed in a specific cell in another worksheet.

This is my current code for drawing value from column no 9 starting from column D, to be placed into another worksheet's cell B14.

Sheet3.Range("B14").Value = Application.IfError(Application.VLookup(Sheet3.Range("B4"), Sheet2.Range("D5:CU530"), 9, False), "Not Found")

This code returns "Not Found" for me.

Further testing using the similar formula but placed directly into a random cell returns the true value. Below is the formula used. 

=IFERROR(VLOOKUP(B4,'Sheet 2 Name'!D5:CU530,9,FALSE),"Not Found")

The same vba code (as shown in first block quote) used for other columns (eg column 2/3/4) are returning the true value instead of "Not Found", even when the destination cell is Sheet 3's B14.

Any ideas why?

Edit: More information If I were to use vLookUp alone in VBA (see next code), I can get the true value, but I will need the IfError as there will be situations where no value are found. 

 Application.VLookup(Sheet3.Range("B4"), Sheet2.Range("D5:CU530"), 9, False)
3
excelvbavlookup
Actually your first piece of code says Sheet3.Range("B14").Value so you put this into B14 instead of B4 - Tom K.
Ah my bad, typo. I want it to be at B14. - kckckc
The code seems fine to me. Maaaaybee the Application.IfError + the Application.VLookup (as in using the Application object twice in the same command) causes the error, but I wouldn't know why. As a workaround I would suggest running the VLOOKUP without the IFERROR and check for erros afterwards. - Tom K.
Hmm that's weird though, because in other cells I'm calling the same command but just gathering data from another column instead of column 9, and it is working. I'll keep troubleshooting and update the post here. thanks for the suggestion though, appreciate it - kckckc
Perhaps it is something related with the value expected to be returned. Would you tell us what's the value that should be returned from column 9. Somehow it seems that the VLookup is returning the correct value but value in column 9 is seen as an error by the IfError function. - EEM

3 Answers

0
votes

You could do without IfError and do an if statement after your Vlookup instead:

x = Application.VLookup(Sheet3.Range("B4"), Sheet2.Range("D5:CU530"), 9, False)

If IsError(x) Then
    Sheet3.Range("B14").Value = "Not found"
Else
    Sheet3.Range("B14").Value = x
End If
0
votes

I believe the error may lie in the vlookup section, vlookup as a much older section of code (i.e. an older version of VB) and where you can, I would really encourage the use of an index match combo, as it is much more specific in what you look for (and more efficient)!

Here is a link to information on Index

https://msdn.microsoft.com/en-us/library/office/ff197581.aspx

and Match

https://msdn.microsoft.com/en-us/library/office/ff835873.aspx

Hope this helps!

-1
votes

use on error funtion

On Error Resume Next

ws.Range("B14").Value = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(ws.Range("B2").Value, ws.Range("E:F"), 1, False), "No error")