Using While Wend with Cells.Find

[Billy]

I import data into a worksheet in Excel2007. I want to find the statement "Stop by Reason", and delete it and some rows above and below it. It might appear 1 time, or up to 5 or 6 times. The follwing code will find it, but once all occurances been found and deleted, I get this messsage...
Run time error '91':
Object variable or With block variable not set.

This is the code...
Sub DeleteStopReason()
While Cells.Find(What:="Stop by Reason", After:=ActiveCell, LookIn _
:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-9, 0).Range("A1:A14").Select
Selection.EntireRow.Delete
ActiveCell.Select
Wend
End Sub
I do not understand what must be done to coreect this sothat all occurcnes will be deleted and the macro not stop.

Thanks,

Billy

 

[Don Guillett]

I import data into a worksheet in Excel2007. I want to find the statement"Stop by Reason", and delete it and some rows above and below it. It mightappear 1 time, or up to 5 or 6 times. The follwing code will find it, but once all occurances been found and deleted, I get this messsage...

Run time error '91':

Object variable or With block variable not set.



This is the code...

Sub DeleteStopReason()

While Cells.Find(What:="Stop by Reason", After:=ActiveCell, LookIn _

:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _

xlNext, MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(-9, 0).Range("A1:A14").Select

Selection.EntireRow.Delete

ActiveCell.Select

Wend

End Sub

I do not understand what must be done to coreect this sothat all occurcnes will be deleted and the macro not stop.



Thanks,



Billy

Look in the vba help index for FINDNEXT. Try that and post again after if you still can't get it.