Find text error

[rick]

With the following code, it finds the text, "red time" and
then correctly pastes the whole selection on the sheet
named "found". But I have this within a loop, and if the
text is not found, then different text is pasted in the
A4250 area, and it is to try another find on that text.
If the text is found on the first time thru the loop, it
works correctly, pasting the selection on the "found"
sheet.... and continues on thru the loop, looking at each
successive pieces of text, and (correctly) NOT finding "red
time" in any of the successive ones, it completes the loop
without any errors given.
If the text is NOT found on the FIRST time thru the
loop, it gives this error:
"Object variable or With block variable not set", and it
hilites this:
If .Find(What:="red time", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate Then

--------------------------------------------
Range("A4250").Select
With Selection

If .Find(What:="red time", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate Then

Selection.Copy
Sheets("found").Select
Range("A4250").Select
ActiveSheet.Paste
Else
End If

End With

 

[Dave Peterson]

It's better to look to see if the .find was successful before you try to
..activate it.

Something like:

Option Explicit
Sub test01()
Dim FoundCell As Range

With Worksheets("sheet1").Range("yourfirstrangehere")
Set FoundCell = .Cells.Find(what:="red time", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
'do your real stuff
End If
End With

If FoundCell Is Nothing Then
'it wasn't found so do the second range:
With Worksheets("sheet1").Range("yourfirstrangehere")
Set FoundCell = .Cells.Find(what:="red time", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
'do your real stuff
End If
End With
End If

End Sub


=====
There's a nice example in VBA's help for .find. It shows how to find to
everything on a worksheet--it continues to find stuff until it finds the same
cell it started on. (It may not be applicable in your case, but someday....)

(watch out for typos. I didn't compile it.