Run Time Error for Vlookup & Match command

Hi Friends,

1. I use the following codes for vlookup

private sub combobox1_change()

TextBox1 = Application.WorksheetFunction.VLookup(CLng(ComboBox1), Sheet6.Range("a2:b65536"), 2)

it is working properly, But when we reached blank record by scrolling through comboBox the run time error 13 saying type mismatch appears. How can we overcome this problem?

2.The same thing with match Command

Privat sub textbox1_afterupdate()

dim res as variant
res = Application.WorksheetFunction.Match(TextBox2, Sheet6.Range("b2:b65536"), 0)

Textbox3 = Sheet6.Range("b1").Offset(res, -1).Value

it is also working properly, But when the record does not match then run time error 1004 appears. I would like to display a message "Record not Found" at this moment.

Thanks


Syed Haider Ali

Possibly use error handling

private sub combobox1_change()
On Error Resume Next
TextBox1 = Application.WorksheetFunction.VLookup(CLng(ComboBox1),
Sheet6.Range("a2:b65536"), 2)
If Err.number <> 0 then
msgbox "Record not found"
Err.clear
End if
On Error goto 0
End Sub

--
Regards,
Tom Ogilvy

"Syed Haider Ali"
<Syed.Haider.Ali.1vm8if_1127149530.96@excelforum-nospam.com> wrote in
message news:Syed.Haider.Ali.1vm8if_1127149530.96@excelforum-nospam.com...
>
> Hi Friends,
>
> __1._ I use the following codes for vlookup
>
> private sub combobox1_change()
>
> TextBox1 = Application.WorksheetFunction.VLookup(CLng(ComboBox1),
> Sheet6.Range("a2:b65536"), 2)
>
> it is working properly, But when we reached blank record by scrolling
> through comboBox the run time error 13 saying type mismatch appears.
> How can we overcome this problem?
>
> _2._The same thing with match Command
>
> Privat sub textbox1_afterupdate()
>
> dim res as variant
> res = Application.WorksheetFunction.Match(TextBox2,
> Sheet6.Range("b2:b65536"), 0)
>
> Textbox3 = Sheet6.Range("b1").Offset(res, -1).Value
>
> it is also working properly, But when the record does not match then
> run time error 1004 appears. I would like to display a message "Record
> not Found" at this moment.
>
> Thanks
>
>
> Syed Haider Ali
>
>
> --
> Syed Haider Ali
> ------------------------------------------------------------------------
> Syed Haider Ali's Profile:
http://www.excelforum.com/member.php...o&userid=21994
> View this thread: http://www.excelforum.com/showthread...hreadid=468870
>