Hi Yash,

The easiest way to do this is to set a results of the Find method to a Range
object, which you're probably already doing. Then you can just see if the
reference Is Nothing - if so, the Find didn't return a reference.

Here's an example:

Sub demo()
Dim sSearch As String
Dim rngSearch As Range

sSearch = "test"

Set rngSearch = Sheet1.UsedRange.Find(sSearch)
If Not rngSearch Is Nothing Then
'/ do your stuff here
Else
MsgBox "Unable to find '" & sSearch & _
"'.", vbExclamation, "Not Found"
End If

Set rngSearch = Nothing
End Sub

Alternatively, you could trap for error 91:

On Error Resume Next
'/ your code that may cause err 91
If Err.Number = 91 Then
MsgBox "Error"
End If

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

wrote:
Hi All,

I am working in excel VBA. I have a userform with a combo box. I have
a list of names in a worksheet.

I need a textbox to populate the employee code with respect to the
name in the combo box.

To achieve this I have used 'find' function to find the name on
combobox_change event which finds the name and shows up the
corresponding employee code.

This works fine till the user selects from the dropdown. However, if i
am trying to type in a name which does not exist on the sheet, it
gives me Run-time error " 91 - Object Variable or with block variable
not set" which is correct.

I want to capture this event and inform the user that this name does
not exist in the spreadsheet.

Can someone please help me to do this. Thanks in advance.

Yash