Variations on the For Next Loop

In a For...Next loop, it is possible to have the loop variable jump up by something other than 1. You might use it to apply greenbar formatting to every other row in a dataset, for example. In this case, you would want to have the counter variable I examine every other row in the dataset. Indicate this by adding the Step clause to the end of the For statement.

FinalRow = Cells(65536, 1).end(xlup).row For I = 2 to FinalRow Step 2

Cells(I, 1).resize(1, 8).Interior.ColorIndex = 35 Next i

While running this code, VBA adds a light green shading to Rows 2, 4, 6, and so on (see Figure 5.9).

The Step clause can easily be any number. You might want to check every tenth row of a dataset to extract a random sample. In this case, you would use Step 10:

FinalRow = Cells(65536, 1).end(xlup).row NextRow = FinalRow + 5

Cells(NextRow-1, 1).value = "Random Sample of Above Data" For I = 2 to FinalRow Step 10

Cells(I, 1).resize(1, 8).Copy Destination:=Cells(NextRow, i) NextRow = NextRow + 1 Next i

0 0

Post a comment