MrExcel.com - Your One Stop for Excel Tips & Solutions

VBA for Exce 97 -- "find" method

Posted by Mindy on May 24, 2001 11:07 AM

Hi
I have a macro using find method to search the match cell value. If matched it returns the row number, if there is no match, it gives me the error message.

"Run-time error '91':
Object variable or with block variable not set."

How do I correct the macro if there is no match row, add the new row in the sheets?

any help will be real appricated. Thank you very much.

Posted by Barrie Davidson on May 24, 2001 11:18 AM

How about something like this:

On Error GoTo Error_Handler
Your code......
Exit Sub
Error_Handler:
If Err.Number = 91 Then
Your code to insert a row.....
Exit Sub
Else
End If

End Sub

Regards,
Barrie

Posted by Tuc on May 24, 2001 11:34 AM


If you are using the Find method of a range object, it returns a range object. That is why you are getting an error 91. You can interogate whether or not the range object that is returned to see if it is actually there or not. Something like:

' Declare variable
dim rngSearch as range

' Invoke the find method to search the cells of the active worksheet, in address "a1:b100", looking for a value of "YourValueHere"
set rngSearch = activesheet.range("A1:B100").Find What:="YourValueHere"

if rngSearch = nothing then
'Nothing was found
debug.print "Nothing was found"
else
' Something was found
debug.print rngsearch.value
debug.print rngsearch.address
end if

Tuc

Posted by Dave Hawley on May 24, 2001 9:20 PM

Hi Mindy

If you are parsing the Row number to a variable and no match is found your varibale will have a value of Zero. So All you need is the "On Error Resume Next" Statement to keep going. See below:

Sub LookFor()
Dim lRw As Long
On Error Resume Next
lRw = 0
lRw = Cells.Find(What:="23", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
If lRw = 0 Then
MsgBox "No match"
End If
On error goto 0
End Sub


The "On error goto 0" will allow any other Run time errors. It is good practice to turn errors back on so you can pick up other Run time errors.

I would also recommend setting the LookIn, LookAt, SearchOrder, and MatchByte arguments EACH time you use the Find method as they will ALWAYS default to the last settings used.


Dave

OzGrid Business Applications