Hi Guys,

Below is a code that searches for a value in a cell in excel. I want a code that would check to see if the word has been found or not. When I do the search and the word is not found, I get an error message " Object Variable or With block variable not set".

I know the above message comes up only when the word is not found. How can I by pass this inorder to tell the user through a message box that the document does not exist. That way the will be able to create a new record.

word = frmSearch.txtEnterDate.Text
If frmSearch.cboSystems.ListIndex = 0 Then
Sheets("Current").Select
Range("A1").Select
'Selection.End(xlDown).Select
Range("A1:A65536").Find(What:=word, LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByColumns).Activate

INI

Never mind guys :), I think I figured it out ! I had to insert the error handler and it work.

Dim power As Variant
word = frmSearch.txtEnterDate.Text
If frmSearch.cboSystems.ListIndex = 0 Then
Sheets("Current").Select
Range("A1").Select
'Selection.End(xlDown).Select
On Error GoTo ErrorHandler
Range("A1:A65536").Find(What:=word, LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByColumns).Activate
ErrorHandler:
   power = CVErr(Err.Number)
If IsError(power) Then
    MsgBox ("Word not found")
End If
 
End If

I have seen people cut an paste screen shots in the threads, how is taht done?

ctrl-printscreen seems to copy a screenshot (in XP anyways) to the clipboard..... then you can run mspaint (start run mspaint) and paste it into paint, in which case you can manip the image...... then when posting your thread (in advanced mode) there is an option to attach a file.... save the file in mspaint and attach it in the thread.....voila.

Never mind guys :), I think I figured it out ! I had to insert the error handler and it work.

Dim power As Variant
word = frmSearch.txtEnterDate.Text
If frmSearch.cboSystems.ListIndex = 0 Then
Sheets("Current").Select
Range("A1").Select
'Selection.End(xlDown).Select
On Error GoTo ErrorHandler
Range("A1:A65536").Find(What:=word, LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByColumns).Activate
ErrorHandler:
power = CVErr(Err.Number)
If IsError(power) Then
MsgBox ("Word not found")
End If
 
End If
This article has been dead for over six months. Start a new discussion instead.