0
Thanks

A few words of thanks would be greatly appreciated.

Excel 2010/VB- Loop until no result is found

Issue

I'm trying to manipulate data from a very old DOS program. I have a lengthy macro that works well until the end where it seaches for some specific words and loops deleting the rows with these words in it until it can't find anything then comes up with an error and stops. What I'm trying to do is get the script to keep running until it doesn't find anymore instances of the search words and then carries on to the next part of the script that we want to write.

Here's the coding loop where it gets stuck:

Dim lCount As Long  
Dim lNum As Long  


lCount = 0  
lNum = 11  

Application.ScreenUpdating = False  


Do While lNum > 10  

Columns("B:B").Select  
Selection.Find(What:="CE - Parish Currently", After:=ActiveCell, LookIn:=xlFormulas, _  
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _  
MatchCase:=False, SearchFormat:=False).Activate  
'  
ActiveCell.Select  
ActiveCell.Offset(-1, 0).Select  
Selection.EntireRow.Delete  
Selection.EntireRow.Delete  
Selection.EntireRow.Delete  
Selection.EntireRow.Delete  
Selection.EntireRow.Delete  
Selection.EntireRow.Delete  
Selection.EntireRow.Delete  
Selection.EntireRow.Delete  
Selection.EntireRow.Delete  

Loop 

We want to do this same function in a few other scripts we've been writing and my boss and I are both novices at this so any help you might be able to offer is much appreciated!

Solution

The code says

Do While lNum > 10  

So when does lNum ever becomes less than or equal to 10 to terminate the loop
Since you have not given all info, this I am thinking is the most logical solution

  Do While True 
      Set Cell = Columns(2).Find(What:="CE - Parish Currently", _ 
                                 After:=Cells(1, 2), _ 
                                 LookIn:=xlFormulas, _ 
                                 LookAt:=xlPart, _ 
                                 SearchOrder:=xlByRows, _ 
                                 SearchDirection:=xlNext, _ 
                                 MatchCase:=False, _ 
                                 SearchFormat:=False) 
    
      If Cell Is Nothing Then Exit Do 
      Cell.EntireRow.Delete 
   Loop

Thanks to rizvisa1 for this tip.

0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel 2010/VB- Loop until no result is found », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).