Syntax Iferror Problem

Hi,

I'm trying to use the iferror function in VBA and the whole code is not executing, as the syntax error is being noticed first, and not allowing the whole code to execute. What I want to happen is if the vlookup returns an error, the cell value should be "*". The code is:

Range("F8").Value = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(" * " & InputBox("Enter Item Code") & " * ", Range("I86:I1500"), 1, False), "*")

If I enter a correct entry into the input box, the vlookup works perfectly but if the vlookup returns an error, the syntax error is appearing first, and not allowing the iferror to work. How can I get around this and make sure the iferror returns * if the vlookup doesn't work?

Cheers,
Benno

What, exactly, are you trying to do?

Thanks for your quick response.

I have attached a demo spreadsheet of what I'm trying to do. F8 should return the vlookup value. If you input A to F into the inputbox, it will return that value, but if you input G, it will error out in VB and not return "No Value" (I changed the error value in this example), as specified in the iferror formula, in VB.

Hope this makes sense. Thanks for your help.

Benno

If you use .WorksheetFunction.VLookup an Error result will cause debug, if on the other hand you opt for Application.VLookup the Error will not cause a debug per se, eg:

Range("F8").Value = Application.WorksheetFunction.IfError(Application.VLookup("*" & InputBox("What item code would you like to search?", "Item Code Search", "*") & "*", Range("I:I"), 1, False), "No Value")

(WorksheetFunctions are generally regarded as being slightly quicker to evaluate)

Note: the above will only work in XL2007 given IFERROR function does not exist in prior versions... the below is an alternative approach

Dim vAns As Variant
vAns = Application.VLookup("*" & InputBox("What item code would you like to search?", "Item Code Search", "*") & "*", Range("I:I"), 1, False)
Range("F8").Value = IIF(IsError(vAns),"No Value",vAns)