Loop that tests and stops if condition is met

[Will85]

My current loop is this:

Let x = 0
Do While x < 7
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 8).Select
    Selection.ClearContents
    Selection.End(xlDown).Select
    x = x + 1
Loop

I would prefer to not have to define the loop, ie 7 iterations, and instead have it test for a condition.

After the first iteration, I want the loop to go to the left one column and down two rows from the last active cell, and if this cell contains the value "x" stop the loop, if it doesn't, then the activecell needs to get back to where it left off before testing for "x", ie one column to the right and two rows up, and then move on to the next iteration of the loop and continue testing until it finds x.

 

[oldbrewer]

so you are in C5 say, move to B7 then move back to C5 ?

 

[Will85]

If it doesnt find an x in B7, then yes, move back to C5 and continue the loop until it finds an x

 

[Mentor82]

My current loop is this:

    Let x = 0
Do While x < 7
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 8).Select
    Selection.ClearContents
    Selection.End(xlDown).Select
    x = x + 1
Loop

I would prefer to not have to define the loop, ie 7 iterations, and instead have it test for a condition.

After the first iteration, I want the loop to go to the left one column and down two rows from the last active cell, and if this cell contains the value "x" stop the loop, if it doesn't, then the activecell needs to get back to where it left off before testing for "x", ie one column to the right and two rows up, and then move on to the next iteration of the loop and continue testing until it finds x.

Hi,
According to what you described you'll be looping 7 iterations between two cells only, ex active cell c5, then move to B7, if no x back to c5 and back to b7. Is that what you want?

Regards,
Sebastian

 

[Will85]

No, I want it to execute some code, then test for x, if it finds x, stop, if not then execute my code again, and so on until it finds x, so:

Step 1:

    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 8).Select
    Selection.ClearContents
    Selection.End(xlDown).Select

Step 2:

Go down two rows, and to the left one column from the active cell, if the value in this cell is "x", then stop if not, move on to step 3

Step 3:

Go to the right one column, up two rows<now were="" back="" to="" the="" last="" active="" cell


Then repeat Step 1, 2, 3. . .</now>

 

[Will85]

so you are in C5 say, move to B7 then move back to C5 ?

Any thoughts?

 

[Mentor82]

Check this, if that's what you're looking for.

regards,
Sebastian

 Sub LoopingTest()
    Dim xFound As Boolean
    Const VALUE_TO_FIND = "x"
    xFound = False
    
    Do While xFound = False
        Selection.End(xlDown).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 8).Select
        Selection.ClearContents
        Selection.End(xlDown).Select
        If LCase(ActiveCell.Offset(2, -1)) = VALUE_TO_FIND Then
            xFound = True
        End If
    Loop
End Sub

 

[Will85]

Bravo Sebastian, thank you.

 

[Mentor82]

You're Welcome! ☺