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 .

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

 

[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