Free Microsoft Excel Training Videos

'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 C1:C10, rounding all the decimal numbers to one decimal place.

Watch the video below to see the first macro in action.


Macro code:
Sub RoundToOneDecimalPlace()
For Counter = 1 To 10
Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
'The curCell value is row 1 and column 3 in the beginning i. e. C1
curCell.Value = round((curCell.Value), 1)
Next Counter
'The next value of the cell to be rounded off will be C2, C3 and so on
End Sub

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 c2:D9, rounding all decimal values to one decimal place.
Sub myround()
'First the range of cells to be acted upon is defined and each cell is defined by variable c
For Each c In Worksheets("sheet1").Range("c2:d9").Cells
'The start cell here is C2
c.Value = round((c.Value), 1)
Next
End Sub
Further links
Loops in Excel
'For next' loop to access worksheet cells and perform calculations