Vlookup #N/A using VBA

[green99]

Is there any way that I can get #N/A using vlookup code in VBA if the corresponding value is not available?
It just gives errors when the value is not found.

On using On Error Resume Next , the error is resolved however, still after trying many ways I am unable to print #N/A.
Kindly help

[Olly]

Post your existing code.

[xlnitwit]

Hi,

If you use Application.Vlookup instead of WorksheetFunction.Vlookup, it will return an error value if the lookup value is not found, rather than causing a run time error. You can store the result in a Variant variable and test it using IsError.

Please Login or Register  to view this content.

[green99]

On Error Resume Next

For i = 2 To 100

Objectm= sheet1.Cells(i, "C").Value
Rangem = sheet2.Range("C:I")

Value1 = Application.WorksheetFunction.VLookup(Objectm, Rangem, 7, False)

If IsError(Value1) Then

Sheet1.Cells(i, "H") = "#N/A"

Else

Sheet1.Cells(i, "H") = Value1

End If

Next

[xlnitwit]

Please use code tags when posting code.

Note that that is not what I suggested, which was to use Application.Vlookup and not WorksheetFunction.Vlookup. Additionally, since you actually want the error value in there, you can use less code

Please Login or Register  to view this content.

[mdmackillop]

Please Login or Register  to view this content.