"unable to get the vlookup property of the WorksheetFunction class"

[westicle47]

I've struggled with using vlookups in vba code for a while now and I have only just found the solution (thanks to skipvought from this thread - thread707-1022186) so I hope this helps. I've looked at loads of threads on this subject and the problem always seems to be same.

The error message "unable to get the vlookup property of the WorksheetFunction class" was appearing whenever the value I was looking up did not appear in the range I was looking in. I couldn't get the IsError function to work because the error occured when the code compiled before it had a chance to check if the vlookup was producing an error. Frustrating to anyone who doesn't doesn't have a good grasp of VBA syntax.

The solution was to remove "WorksheetFunction" from the vlookup code below...

If Application.WorksheetFunction.IsError(Application.WorksheetFunction.VLookup(w, x, y, y)) = True Then blah blah blah

...so that it now reads...

If Application.WorksheetFunction.IsError(Application.VLookup(w, x, y, y)) = True Then blah blah blah

I don't understand exactly why this works so it would be great if someone who knows could answer the question and then maybe this could go in the FAQ.