Cutting a "Do Until" loop once it finds the result.

[RockandGrohl]

Hello all,

This is just out of curiosity, but I have a "stop" on my Do Until loop which says when A is "", stop.

It needs to run down a large list until it finds a criteria, then it saves the contents of whatever cell is in column S as a variable.

Is there a way to halt it once it finds the variable? Because according to this loop:

Do Until Cells(ActiveCell.Row, "A").Value = ""    If Cells(ActiveCell.Row, "A").Value = PapNam Then
        If Cells(ActiveCell.Row, "B") = "JGRT" Then
        JGROI = Cells(ActiveCell.Row, "S").Value
        Else
        OMROI = Cells(ActiveCell.Row, "S").Value
        End If
    End If
ActiveCell.Offset(1, 0).Activate
Loop

Even when it finds that A = PapNam and B is JGRT, and it saves the number in S as a variable for JGROI, it will still loop all the way down 1,000 papers looking for any other instances of the PapNam, even though it's already got the information needs.


Can I say for instance, Do Until Cells(ActiveCell.Row, "A").Value = "" or Variables = Filled


Cheers

 

[Joe4]

Just add "Exit Do" if you want to bail early.

Also, don't use "Select" and "Activate" statements in your loops. Loops are slow and inefficient enough as it is, adding those things can really slow it down.
Identify the range you want to loop through, and loop through without selecting, i.e. structure it like this instead.

Dim cell as Range
Dim rng as Range

Set rng=Range(whatever range you want)

For each cell in rng
    ...
Next cell

To exit that loop early, you would just use an "Exit For".

 

[RockandGrohl]

Just add "Exit Do" if you want to bail early.

Also, don't use "Select" and "Activate" statements in your loops. Loops are slow and inefficient enough as it is, adding those things can really slow it down.
Identify the range you want to loop through, and loop through without selecting, i.e. structure it like this instead.

Dim cell as Range
Dim rng as Range

Set rng=Range(whatever range you want)

For each cell in rng
    ...
Next cell

To exit that loop early, you would just use an "Exit For".

Hi Joe,

So in that example, where the "..." is I would have if "cells(activecell.row, "A").value = PapNam Then" Right? That's where it goes?

 

[Norie]

Why not forgo the loop altogether and use Match?

Res = Application.Match(PapName, Range("A" & ActiveCell.Row & ":A" & Range("A" & Rows.Count).End(xlUp).Row), 0)

If Not IsError(Res) Then
    If Cells(Res, "B") = "JGRT" Then
        JGROI = Cells(Res, "S").Value
    Else
        OMROI = Cells(Res, "S").Value
    End If
End If

 

[Joe4]

So in that example, where the "..." is I would have if "cells(activecell.row, "A").value = PapNam Then" Right? That's where it goes?

That is where your checks would go, but you wouldn't use ActiveCell. It would look like:

If cell.value = PapNam Then

(assuming that you have defined "rng" to be cells in column A)