Do Until Loop with multiple criteria

[rdw72777]

First I apologize if this has been posted before, but "Do" and "until" are not searchable words on this site.

Is it possible to do a "Do Until loop" with multiple criteria. for instance if I wanted to do a "Do Until" with the criteria of cell "A1" being empty or a variable (which is set inside the loop if criteria are met) = a specificed value, is this possible.

I know I could probably do a For-Next Loop with an If statement isnide, but i was just curious if the "Do until" loop offered the ability to insert multiple criteria. When i tried the following it immediately skipped the loop as if the Until criteria had been met, but it hadn't (almost acted like a syntax error except no pop-up, just skipped it.

Sheets("Input").Range("A:EZ").AutoFilter Field:=Check, Criteria1:="=RESP", Operator:=xlOr, Criteria2:="=Respiratory"
        Do Until IsEmpty(ActiveCell) Or DataPresent = True
            If ActiveCell.Value = "RESP" Then DataPresent = True
            If ActiveCell.Value = "Respiratory" Then DataPresent = True
            ActiveCell.Offset(1, 0).Select
        Loop

 

[Norie]

Ryan

Yes it is possible and you look as though you've got the right idea about the syntax for the criteria.

The problem could be something other than syntax, it might be the actual conditions for the criteria.

For example using ActiveCell is never a wise idea, could you not specify the start cell with something like this.

Set rngCell = Worksheets("Input").Range("A2")

Then use that in the rest of the code

Do Until rngCell.Value="" Or DataPresent
   DatePresent = (rngCell.Value = "RESP") Or (rngCell.Value ="Respiratory")
   Set rngCell = rngCell.Offset(1)
Loop

Hope I've got the logic right.

 

[shg]

Another way:

    Dim wks         As Worksheet
    Set wks = Worksheets("Input")
    wks.Range("A:EZ").AutoFilter Field:=Check, _
                                 Criteria1:="=RESP", _
                                 Operator:=xlOr, _
                                 Criteria2:="=Respiratory"
    On Error Resume Next
    DataPresent = wks.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1
    On Error GoTo 0

 

[Marcelo Branco]

Hi,

The original macro didnt work for me with Field:=Check, but worked perfect with Field:=2 (Check is 2nd column on my simulation) and selecting A2 before.

M.

 

[Norie]

Eh, I might be wrong - not an unusual occurence round here - but I thought Ryan was maybe asking about how to use a Do...While loop.

Using Find is fine, if it works, but sometimes it can be easier to just loop.

 

[rdw72777]

Well one of my cohorts wrote this code and he's out of the area this week so I'm trying to re-write it. I'm not sure I need to the Do Until loop at all but I'm trying to preserve his vision until he gets back and can explain to me why he did it this way.

Basically he loops through a list that could have thousands of rows or 0. If any of the rows is eual to "RESP" or "Respiratory" it will evaluate datePresent to true and the loop should end; I'm not sure why he did the Do Until loop for this....oh well.

Thanks for the help, I'll continue testing....

 

[Norie]

Ryan

If all the code needs to do is see if either of those words appear in the range you might just be able to use COUNTIF, or in code:

Dim strVal As String
Dim rng As Range
Set rng = Range("A1:C18")
strVal = "RESP"
If Application.WorksheetFunction.CountIf(rng, "*" & strVal & "*") > 0 Then
    MsgBox "Found " & strVal
End If

 

[rdw72777]

Norie, you're sneaky. I copied your syntax in to work with and couldn't figure out why it didn't work. you changed my variable from DataPresent 9my code) to DatePresent (your code). LOL. Thanks, it works, i'll post final code when done with my changes.

 

[Norie]

I take no responsibility for spelling mistakes and/or typos.

Though if I'd followed my own advice and used Option Explicit I might have caught the error.