Loops For Next Excel VBA

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.

Macro code:
Sub Automation()
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
Next Row
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 D2:D5, highlighting in red all values greater tan or equal to 200000.
Sub highlight()
For Each c In Worksheets(“Sheet1”).Range(“D2:D5”).Cells
If c.Value >= 200000 Then
c.Font.ColorIndex = 3
End If
End Sub

Further reading:

I want to loop through all selected cells and perform an operation on each of them

One thought on “Loops For Next Excel VBA

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.