vlookup, handling wrong lookup value - vba

[lezawang]

Hi
I want to improve the code below so if a user entered a wrong name, then vba will give error message. I tried with IfError() as below but got error message. Thank you so much.

Sub mylookup()
    Dim xname As String
    Dim xnumber As Integer
    xname = InputBox("name")
    xnumber = Application.WorksheetFunction.VLookup(xname, Range("a1:b100"), 2, False)
    'xnumber = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(xname, Range("a1:b100"), 2, False), MsgBox("not good name"))
    MsgBox xnumber
End Sub

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]name[/TD]
[TD]ph#[/TD]
[/TR]
[TR]
[TD]mary1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]mary2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]john3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Try

Sub mylookup()
    Dim xname As String
    Dim xnumber As Variant
    xname = InputBox("name")
    xnumber = Application.vlookup(xname, Range("a1:b100"), 2, False)
    If IsError(xnumber) Then
      MsgBox "Oops"
    Else
      MsgBox xnumber
End Sub

 

[Kamolga]

I would use Find in VBA rather than vlookup, I believe speed of execution is better. You can then use a "on error goto"

Sub mylookup()
    Dim Rng As Range: Set Rng = Range("A:A")
    Dim xname As String: xname = InputBox("Name")
    On Error GoTo Wrongentry
    MsgBox Rng.Find(xname).Offset(0, 1).Value
    Exit Sub
Wrongentry:
   MsgBox ("Not good name")
End Sub

 

[lezawang]

Thank you all for the help. But I am just curious, why I have to change

application.worksheetfunction.vlookup()

to

application.vlookup()

I does not work with worksheetfunction.vlookup()! what is the difference between these both function. Thank you once again.

 

[Fluff]

If you use WorksheetFunction then the code will fail if no match is found.
Without it an error number will be returned to the variable, that you can then check against.