'For next' loop to access worksheet cells and perform
||How to use
'For next' loop
access worksheet cells
and perform calculations: When using Visual Basic for Applications in
Microsoft Excel, you often need to run the same block of statements on
each Excel cell in a range of cells. To do this, you combine a looping
statement and one or more methods to identify each cell,
and run the operation.
One way to loop through a range in Excel is to use the For...Next loop
with the Cells property. Using the Cells property, you can substitute
the loop counter
variables or expressions) for the cell
numbers. In the following example, the variable counter is
for the row index. The procedure loops
through the Excel range D2:D5, calculating all the amounts based on the
unit price and the quantity of each product in cells ranges B2:B5 and
Excel training video below to
see the first macro in action.
For Row = 2 To 5
Set curCell = Worksheets("Sheet1").Cells(Row, 4)
'The curCell value is row 2 and column 4 in the beginning i.
curCell.Value = Cells(Row, 2).Value * Cells(Row, 3).Value
Another easy way to loop through a range in Excel is to use a For
Each...Next loop with the collection of Excel cells specified in the
Range property. Visual Basic automatically sets an object variable for
the next cell each time the loop runs. The following procedure loops
through the Excel range D2:D5, highlighting in red all values
greater tan or equal to 200000.
For Each c In Worksheets("Sheet1").Range("D2:D5").Cells
If c.Value >= 200000 Then
c.Font.ColorIndex = 3