Vlookup error on User Form

[TWaldron78]

Hi folks,

Hoping someone can help me , I have built a user form that searchs a very large excel file and returns selective date , the starting point for this is someone entering an email address in a text box on the form , the macros then find that persons supervisor and location , uses that supervisors name to find his supervisor and so on giving the whole reporting structure in one form.
I have all the code working fine , it does exactly what I need it to do except,when someone enters an email address that isnt in the original file , either through miss typing or it just isnt in the file.
That part of the code that I need help with is below.

How can I get the Macro to not error if the first lookup returns #N/A and give the message box , everything on this works fine and is a huge help to people as long as the email address is correct.

Any and all suggestions greatly accepted.

Thanks


Private Sub CommandButton1_Click()




If TextBox1.Text <> "" Then


TextBox2.Value = Application.WorksheetFunction.VLookup(TextBox1, Worksheets("Test Sheet").Range("G:I"), 3, False)




Else: MsgBox "Please Enter a valid email address" ' This only works if TextBox1 is left completely blank
Exit Sub




End If


TextBox6.Value = Application.WorksheetFunction.VLookup(TextBox1, Worksheets("Test Sheet").Range("G:J"), 4, False)


and so on and so on , every thing from here works

 

[Norie]

Perhaps.

Dim Res As Variant
' other code
Res = Application.VLookup(TextBox1, Worksheets("Test Sheet").Range("G:I"), 3, False)

If IsError(Res) Then 
    MsgBox "Email not found."
    Exit Sub
Else
    TextBox2.Value = Res
End If

By the way, if you are always looking in column G you should consider using Application.Match to get the row the email address is in.

You would only need to do that once.

 

[TWaldron78]

Norie,

Thanks so much , worked a treat.
Very much appreciated.