Microsoft Office Tutorials and References
In Depth Information
When you nest loops, you need to be aware of a few important points:
When you nest For…Next loops, each loop must have its own uniquely named counter
When you nest For…Each…Next loops, each loop must have its own uniquely named object
(or element) variable.
If you use an Exit For or Exit Do statement, only the loop that is currently executing will
terminate. If that loop is nested within a larger loop, the larger loop will still continue to
execute its iterations.
I mentioned this earlier in this lesson, but it especially holds true with nested loops: I strongly
recommend you include the variable name in your Next statements.
Here is an example of a macro with a Do loop nested inside a For…Each…Next loop. This macro will
produce a list of six unique random numbers between 1 and 54, similar to a lottery drawing.
‘Declare the Range variables for the entire six-cell range,
‘and for each individual cell in the six-cell range.
Dim RandomRange As Range, RandomCell As Range
‘Identify the six-cell range where the randomly selected numbers will be listed.
Set RandomRange = Range(“A1:A6”)
‘Before populating the six-cell list range, make sure all its cells are empty.
‘Open a For...Each loop to cycle through each cell in range A1:A6.
For Each RandomCell In RandomRange
‘Open a Do...Loop that enters a unique random number between 1 and 54
RandomCell.Value = Int(54 * Rnd + 1)
Loop Until WorksheetFunction.CountIf(RandomRange, RandomCell.Value) = 1
‘Iterate to the next cell until all six cells have been populated.
In this lesson, you write a macro with a For…Next loop that adds 12 worksheets to a workbook, and
names each of them by month.