:: infotechGuyz.com ::
> For the IT community by the IT community

Excel 2010 Macro For and Next Loops

 

For and Next are common loop constructs. Everything between For and Next is run multiple times. Each time the code runs, a certain counter variable, specified in the For statement, has a different value.

For I = 1 to 10

Cells(I, I).Value = I

Next I

 

As this program starts to run, give the counter variable a name of I. The first time through the code, the variable I is set to 1. The first time the loop is executed, I is equal to 1, so the cell in Row 1, Column 1 will be set to 1.

Let’s look at what happens as VBA gets to the line that says Next I. The variable I is equal to 1 before running this line. During the execution of Next I, VBA must make a decision. VBA adds 1 to the variable I and compares it to the maximum value in the To clause of the For statement. If it is within the limits specified in the To clause, the loop is not finished, in which case, the value of I will be incremented to 2. Code execution then moves back to the first line of code after the For statement.

The second time through the loop, the value of I is 2. The cell in Row 2, Column 2 (cell B2) gets a value of 2. As this continues, the Next I statement advances I up to 3, 4, and so on. On the tenth time through the loop, the cell in Row 10, Column 10 is assigned a value of 10.

The common use for such a loop is to walk through all the rows in a dataset and decide to perform some action based on some criteria.

For example, if you want to mark all the rows with positive service revenue in Column F, you could use this loop:

For I = 2 to 10

    If Cells(I, 6).Value > 0 Then

Cells(I, 8).Value = “Service Revenue”

Cells(I, 1).Resize(1, 8).Interior.ColorIndex = 4

    End If

Next i

 

This loop checks each item of data from Row 2 through Row 10. If there is a positive number in Column F, Column H of that row will have a new label, and the cells in Columns A:H of the row will be colored green.

It’s possible to use a variable to specify the upper/lower limit of the For statement.

This code sample identifies FinalRow with data and then loops from Row 2 to that row:

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

For I = 2 to FinalRow

    If Cells(I, 6).Value > 0 Then

Cells(I, 8).Value = “Service Revenue”

Cells(I, 1).Resize(1, 8).Interior.ColorIndex = 4

    End If

Next I