Keep getting the same error when I can't find the text. Basically, I have
the code doing exactly what I want when I find "final summary". That is,
deleting everything from "final summary" down. But when it doesnt find it, I
get the error. I looked here and tried some of the ways w/ setting the
range. When it didn't find it, it worked great. But when it did find it, it
doesn't do what I wanted it to do. Here is the code that works fine when it
finds "final summary" and errors when not:

Cells.Find(what:="final summary", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents

But, I can't get it to work when I try the range method. So, the code above
works fine when it finds "final summary" and errors when not. And the range
method works fine when it doesn't find "final summary", but doesn't delete
"final summary" and everthing below it. Any ideas/suggestions?

Dim rng as Range
set rng = Cells.Find(what:="final summary", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
if not rng is nothing then
rng.Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
else
msgbox "final summary not found"
End if

--
Regards,
Tom Ogilvy


"escudolm" wrote in message
...
Keep getting the same error when I can't find the text. Basically, I have
the code doing exactly what I want when I find "final summary". That is,
deleting everything from "final summary" down. But when it doesnt find
it, I
get the error. I looked here and tried some of the ways w/ setting the
range. When it didn't find it, it worked great. But when it did find it,
it
doesn't do what I wanted it to do. Here is the code that works fine when
it
finds "final summary" and errors when not:

Cells.Find(what:="final summary", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents

But, I can't get it to work when I try the range method. So, the code
above
works fine when it finds "final summary" and errors when not. And the
range
method works fine when it doesn't find "final summary", but doesn't delete
"final summary" and everthing below it. Any ideas/suggestions?

When writing code using Find, it is best to structure the code as
follows:

Dim FoundCell As Range
Set FoundCell = Cells.Find(...)
If FoundCell Is Nothing Then
' not found
Else
FoundCell.Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"escudolm" wrote in message
...
Keep getting the same error when I can't find the text.
Basically, I have
the code doing exactly what I want when I find "final summary".
That is,
deleting everything from "final summary" down. But when it
doesnt find it, I
get the error. I looked here and tried some of the ways w/
setting the
range. When it didn't find it, it worked great. But when it
did find it, it
doesn't do what I wanted it to do. Here is the code that works
fine when it
finds "final summary" and errors when not:

Cells.Find(what:="final summary", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents

But, I can't get it to work when I try the range method. So,
the code above
works fine when it finds "final summary" and errors when not.
And the range
method works fine when it doesn't find "final summary", but
doesn't delete
"final summary" and everthing below it. Any ideas/suggestions?

Thanks for the help. I'll see if it works.