Using Variables in the For Statement

The previous example is not very useful in that it works only when there are exactly 10 rows of data. It is possible to use a variable to specify the upper and/or lower limit of the For statement. This code sample identifies the FinalRow with data, and then loops from Row 2 to that row:

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

Cells(I, 8).value = "Service Revenue" Cells(I, 1).resize(1, 8).Interior.ColorIndex = 4 End If Next i

Be aware when using variables. What if the imported file today is empty and has only a heading row? In this case, the FinalRow variable is equal to 1. This makes the first statement of the loop essentially say, "For I = 2 to 1." Because the start number is higher than the end number, the loop does not execute at all. The variable I is equal to 2 and code execution jumps to the line after Next.

0 0

Post a comment