|
|
'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
Next
End Sub
|
|
|
|
|