When to use For Next

[Alpacino]

Can someone just summarise when you would use this?? and give an example please. I understand If/Then/EsleIf and Case but struggling to with this

Regards

Alan

 

[gbell3587]

Can someone just summarise when you would use this?? and give an example please. I understand If/Then/EsleIf and Case but struggling to with this

Regards

Alan

Used in a for loop e.g

count = 0
for x = 1 to 10
count = count + 1
next x

 

[Ruddles]

You would use it when you knew exactly how many times you needed to loop round before you entered the loop. For example if you wanted to do something to all the elements of an array, you'd loop from the first element to the last one. If you wanted to do something to rows 17 to 43 of your worksheets, you'd loop from 17 to 43.

On the other hand you'd use Do While or Do Until if you didn't know in advance how many times you needed to loop round, but the conditions which decided when to terminate the loop were going to arise after you'd entered the loop. For example, you might want to loop round until the user provided a particular response, until a worksheet cell contained a particular value, until you reached the end of an input file or until a predetermined time of day, etc.

Does that help?

 

[MrKowz]

Another thing to touch on, to ammend to Ruddle's explanation:

There are a couple way to look at Do While/Until loops:

Do While/Until {Condition}
    'stuff to do in the loop
Loop

And

Do
    'stuff to do in the loop
Loop While/Until {Condition}

The key difference between the two loops is that the first loop can be completely bypassed if the condition is false/true. Under the second loop, it will perform the first iteration of the loop prior to checking the condition.

A couple direct examples of where each type of loop is helpful:

fPath = wbMaster.Path
fName = dir(fPath & "\" & "*.xls")
Do While Len(fName) > 0
    Workbooks.Open(fPath & "\" & fName)
Loop

and

    With Range("B9:B400")
        Set rng = .Find("", LookIn:=xlValues, LookAt:=xlWhole)
        If Not rng Is Nothing Then
            Do
                rng.EntireRow.Hidden = True
                Set rng = .FindNext(rng)
            Loop While Not rng Is Nothing
        End If
    End With