Member Avatar for INI

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

  • 2 Contributors
  • 4 Replies
  • 85 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by INI
Member Avatar for 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
Member Avatar for INI

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

Member Avatar for Comatose

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.

Member Avatar for 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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.