Vba vlookp error

[ali zaib]

Dear all,
i have facing problem in below mention codes these code are working properly if value has match but when value not match its show run time error please suggest .

TextBox5.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 5, 0)</SPAN> TextBox13.Text = WorksheetFunction.VLookup(TextBox5.Value, Sheets("Basis").Range("I1:J100"), 2, 0)</SPAN> TextBox15.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 2, 0)</SPAN> TextBox11.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 3, 0)</SPAN> TextBox12.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 4, 0)</SPAN> TextBox16.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:H60000"), 8, 0)</SPAN> TextBox17.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:H60000"), 6, 0)</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> </SPAN>

</SPAN>

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[VoG]

Try replacing WorksheetFunction with Application

 

[ali zaib]

Sir found same problem

 

[VoG]

Try like this

Dim x As Variant
x = Application.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 5, 0)
If Not IsError(x) Then
    TextBox5.Text = x
Else
    TextBox5.Text = "Not found"
End If

Also, take a look here Daily Dose of Excel » Blog Archive » The WorksheetFunction Method

 

[VoG]

Also, if the textbox contains a number

Dim x As Variant
x = Application.VLookup(Val(TextBox3.Value), Sheets("Basis").Range("A1:F60000"), 5, 0)
If Not IsError(x) Then
    TextBox5.Text = x
Else
    TextBox5.Text = "Not found"
End If

 

[ali zaib]

Can we use all this formula for combine code at once??

 

[VoG]

You can re-use the variable x