Can you reliably replace a "Do Until" loop with Find? Is it faster?

[RockandGrohl]

My predecessor wrote a lot of VBA code for a lot of programs, and these were written across the years where his capabilities evolved and you can see various levels of sophistication as you look through his old sheets.

Something he's relied on time and time again is a Do Until loop where each cell in a list, 10's, 100's or 1000's and even 10,000's long is checked for some logic.

An example would be a simple thing like:

Do Until Cells(ActiveCell.Row, "C").Value = ""    If Cells(ActiveCell.Row, "E").Value = AdSelect.PapNam Then
    Exit Do
    End If
ActiveCell.Offset(1, 0).Activate
Loop

Now when it finds the cell it wants, the code progresses onwards and various variables are set.

Now obviously Excel spits through these in milliseconds, but is there a faster way?


My project is to reduce the amount of time a userform takes to open, we're talking 12-20 seconds each time. I have some ideas to improve efficiency but I was wondering if a Do Until loop can be replaced wholesale with a find?


Could I for instance say Find where [Criteria 1], [Criteria 2] is applicable instead of checking every line?


So in another example, I could want to find where a newspaper name AND a Template size match what I have held in memory, "Swindon Standard" and "16x10"

I would want to find where "Swindon Standard" sits in column A AND where "16x10" also sits on column C.

Cheers.

 

[RockandGrohl]

Some other performance improvements I've got in mind are where to open a userform, the macro has to open 5 other spreadsheets to determine data. This data doesn't really change much across the week, so I'm thinking of having it all sit on individual hidden tabs. Then when the macro needs the data it can just look in the tab instead of opening spreadsheets over and over.

The problem is we have a Userform we need to open 600+ times, it takes 20 seconds to open. That's 3.3 hours of sitting there waiting every week.

If we can get that down to 10 seconds or even 5 that's a significant time saving improvement.


There are some large chunks of code that look for logic in a loop, sometimes they use For x loops.. I'm happy to post them here if anyone is feeling brave at helping optimise.

 

[steve the fish]

Something like this will find the first simultaneous occurance of whatever is in E1 in column A and whatever is in F1 in column C and produce the row number. Its quicker than a loop.

With Sheets("Sheet1")
    rw = Evaluate(Replace("MATCH(1,('xxx'!A:A='xxx'!E1)*('xxx'!C:C='xxx'!F1),0)", "xxx", .Name))
End With
MsgBox "Found on row " & rw

 

[RockandGrohl]

Something like this will find the first simultaneous occurance of whatever is in E1 in column A and whatever is in F1 in column C and produce the row number. Its quicker than a loop.

With Sheets("Sheet1")
    rw = Evaluate(Replace("MATCH(1,('xxx'!A:A='xxx'!E1)*('xxx'!C:C='xxx'!F1),0)", "xxx", .Name))
End With
MsgBox "Found on row " & rw

That looks like it'll work.

So from what I can gather my predecessor built his sheets to do this sort of thing.

Loop through the list until various criteria are met, when those criteria are met it will take certain values on that row and set them as variables to be entered elsewhere.

All his logic is doing is just starting from the top and going down row by row until the active cell sits on the first row where criteria are met.


In a moment I'll post a big chunk of code that takes 3 seconds to chew through. That's one of the big areas for improvement.