Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA code to terminate a find loop
When I import a report & run the following VBA code to delete the headings, after the last heading is deleted, I get: Runtime Error '91": Object Variable or With block variable not set What VBA code should be used to terminate the loop to prevent the error? mikeburg Sub CDeleteHeadings() Cells.find(what:="Name & Address", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Do ActiveCell.Offset(rowoffset:=0).EntireRow.Delete ActiveCell.Offset(rowoffset:=0).EntireRow.Delete Cells.findnext(After:=ActiveCell).Activate Loop Until Cells.findnext = False -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=381737 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA code to terminate a find loop
You can try modifying your code by changing
Loop Until Cells.findnext = False to Loop Until Cells.findnext is nothing (untested) or try this code which should be more efficient... Sub Test Call DeleteUnwanted("Name & Address") End Sub Sub DeleteUnwanted(ByVal DeleteWord As String) Dim wks As Worksheet Dim rngToSearch As Range Dim rngCurrent As Range Dim rngDelete As Range Dim rngFirst As Range Set wks = Sheets("Sheet1") Set rngToSearch = wks.Cells Set rngCurrent = rngToSearch.Find(DeleteWord) If Not rngCurrent Is Nothing Then Set rngDelete = rngCurrent Set rngFirst = rngCurrent Do Set rngDelete = Union(rngCurrent, rngCurrent.Offset(1,0), rngDelete) Set rngCurrent = rngToSearch.FindNext(rngCurrent) Loop Until rngCurrent.Address = rngFirst.Address rngDelete.EntireRow.Delete End If End Sub -- HTH... Jim Thomlinson "mikeburg" wrote: When I import a report & run the following VBA code to delete the headings, after the last heading is deleted, I get: Runtime Error '91": Object Variable or With block variable not set What VBA code should be used to terminate the loop to prevent the error? mikeburg Sub CDeleteHeadings() Cells.find(what:="Name & Address", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Do ActiveCell.Offset(rowoffset:=0).EntireRow.Delete ActiveCell.Offset(rowoffset:=0).EntireRow.Delete Cells.findnext(After:=ActiveCell).Activate Loop Until Cells.findnext = False -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=381737 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA code to terminate a find loop
Rearrange some things and test for Is Nothing rather than False and you've got it. Sub CDeleteHeadings() Do Cells.Find(what:="Name & Address", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(rowoffset:=0).EntireRow.Delete ActiveCell.Offset(rowoffset:=0).EntireRow.Delete Loop Until Cells.FindNext Is Nothing End Sub HTH -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=381737 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop or Find code ?? need help !! | Excel Worksheet Functions | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
How to Loop some code | Excel Discussion (Misc queries) | |||
VBE code in a loop | Excel Programming | |||
code to terminate access after certain time | Excel Programming |