VB Excel 2010 - Loop until can't find [Solved/Closed]

whitepanther - Jun 6, 2011 at 11:55 PM - Latest reply:  whitepanther
- Oct 26, 2011 at 08:33 PM
Hello,

We are trying to manipulate data from a very old DOS program. We 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 we're 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!



See more 

25 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 7, 2011 at 02:31 AM
2
Thank you
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

Thank you, rizvisa1 2

Something to say? Add comment

CCM has helped 1699 users this month

Sorry to revisit this but I've been using this code you gave me in loads of macros and it's been working perfectly until this recent one I'm working on. I've studied it and studied it but can't quite figure out where I've gone wrong! The problem is it doesn't seem move onto the next row to keep searching and so keeps picking up the same 1st match it finds for the search term and running the loop on that one line. Here's what I've got written:

Do While True
Set Cell = Columns(2).Find(What:="D/W", _
After:=Cells(1, 2), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Cell Is Nothing Then Exit Do
Cell.Select
Cell.Offset(0, 3).FormulaR1C1 = "12/31/2011"
Cell.Offset(0, 4).FormulaR1C1 = "=RC[-1]-RC[-2]"
Cell.Offset(1, 4).FormulaR1C1 = "=RC[-5]/(R[-1]C/7)"
Cell.Offset(1, 4).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
ActiveCell.Cut
Cell.Offset(0, 4).Select
ActiveSheet.Paste
Cell.Offset(1, -1).Select
ActiveCell.Cut
Cell.Offset(0, 3).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select

Loop

Are you able to see where the problem might be?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Oct 21, 2011 at 06:41 AM
see this
After:=Cells(1, 2), _
I'm afraid I'll need you to be more specific... I've already played around with those numbers with the same result each time. It simply keeps picking up the same first cell each time it searches.

As I read it right now it says to look after row 1 column 2, which is what I want it to do... I've tried After:=Cells(2, 2), _ which starts by picking up the 2nd instance of the search term but again it just loops around focusing on that 1st find.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Oct 26, 2011 at 06:56 PM
"After:=Cells(1, 2), " is saying that next time search from cells(1,2). So each time you say that look from cells(1,2) it will always find the same thing. You need to tell it that for next search where to start next search. See the other code that was provided. See how in that how where to start next search is change
Ah... ok. It took me a while to figure it out but I think I finally understand. I think everytime I've used this loop I must have moved or deleted the first instance of the search term so the code just continued on finding the next instance. With this particular code I didn't do that - no wonder it wasn't working!

Ok, go on call me a dim-wit... I know you want to! - to my credit I've had a cold that's lasted over a week now so my head hasn't been that clear :-/

Thanks for perservering with explaining!