Display a message box when vlookup returns or does not return a match

Vote count:

0

I am trying to display a message box* indicating when a vlookup formula matches a name in array and when it does not.

With the below data, my current code works fine:

Name Name
John John

It returns the following message box: "Ok"

Sub check_name()
Dim name As String
name = Range("A2")

If Application.WorksheetFunction.VLookup(Range("A2"), Range("B1:B2"), 1, False) = name Then
    MsgBox "Ok"
Else
    MsgBox "Not Ok"

End If
End Sub

But the code does not work when there is not match.

With the below data, I'm trying to display: "Not Ok".

Name Name
John Wayne

I get the following message:

Run-time error '1004'

Application-defined or object-defined error

I guess the If statement does not work when the vlookup does not return a match, i.e, if it throws an error like #N/A.

_{I've tried to define the variable name as Boolean. making it equal the vlookup formula wrapped with IsNA, and then I tried to use 'name' inside an If statement, but I got the same pattern of results presented above.}

How I can get through this? Tks.

* _{Looking for answers using VBA}

asked 40 secs ago

Andre Silva

2,057