For Next Loop to access worksheet cells and perform calculations
Looping Process = Automation
How to use ‘For next’ loop to 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, one at a time, 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 (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted 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 C2:C5.
Watch the 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. e. D2
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