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(ComboBo x1),
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

Run Time Error for Vlookup & Match command
 

Possibly use error handling

private sub combobox1_change()
On Error Resume Next
TextBox1 = Application.WorksheetFunction.VLookup(CLng(ComboBo x1),
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"
wrote in
message news:Syed.Haider.Ali.1vm8if_1127149530.96@excelfor um-nospam.com...

Hi Friends,

__1._ I use the following codes for vlookup

private sub combobox1_change()

TextBox1 = Application.WorksheetFunction.VLookup(CLng(ComboBo x1),
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