Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using While Wend with Cells.Find
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using While Wend with Cells.Find
On Tuesday, August 14, 2012 11:14:35 AM UTC-5, Billy wrote:
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 Look in the vba help index for FINDNEXT. Try that and post again after if you still can't get it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WEND in VBA | Excel Discussion (Misc queries) | |||
Simple code to find the empty cells in a range and cells with number | Excel Programming | |||
Need Cells.find to find first number in a row which is 8000 | Excel Discussion (Misc queries) | |||
While-Wend loop to fill in nulls | Excel Programming | |||
While ... Wend | Excel Programming |