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 |
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. |
All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com