For-Each Loops in Excel VBA
CCE 170 - Brigham Young University
Norm Jones
Traversing through a range of cells or a
collection of objects in a systematic fashion
Doing calculations that require iterations
Repeating actions until some condition is met
For Each…
For i=1 To …
Do While…
Do Until…
For Each…
loops are used to loop through a
set of items. For example
Cells in a range
Objects in a collection
We have already used
For Each…
loops, but
let’s review them
Dim
myitem
As Variant
For Each
myitem
In
mycollection
statement(s)
Next
myitem
Dim
mycell
As Range
For Each
mycell
In
Range(“B4:D20”)
If
mycell.Value < 0
Then
numneg = numneg + 1
End If
Next
mycell
Dim
sh
As Shape
For Each
sh
In
Shapes
sh.Fill.ForeColor.RGB = vbRed
Next
sh
For i=…
loops are used in situations when you
know exactly how many times you need to go
through the loop
They are useful for traversing through cells or
for making computations
Dim
i
As Integer
For
i = Start
To
End
Step
inc
statement(s)
Next
i
Dim
myrow
As Integer
For
myrow = 3
To
17
Cells(myrow, 2) = "Hello"
Next
myrow
Dim
myrow
As Integer
For
myrow = 17
To
3
Step
-1
Cells(myrow, 2) = "Hello"
Next
myrow
Dim
i
As Integer
Dim
sum
As Integer
For
i = 1
To
100
sum = sum + i
Next
i
You can exit any “For" loop at any time using
the
Exit For
statement
This kicks you out of the loop and it moves
the execution to the next statement just
outside the loop
Explore the concept of for-each loops in Excel VBA, which provide a structured way to iterate through collections of items such as cells or objects. Learn how to use for-each loops efficiently for traversing data, making computations, and performing repetitive actions until specific conditions are met.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
CCE 170 - Brigham Young University Norm Jones
Traversing through a range of cells or a collection of objects in a systematic fashion Doing calculations that require iterations Repeating actions until some condition is met
For Each For i=1 To Do While Do Until
For Each loops are used to loop through a set of items. For example Cells in a range Objects in a collection We have already used For Each loops, but let s review them
Dim myitem As Variant For Each myitem In mycollection statement(s) Next myitem
Dim mycell As Range For Each mycell In Range( B4:D20 ) If mycell.Value < 0 Then numneg = numneg + 1 End If Next mycell
Dim sh As Shape For Each sh In Shapes sh.Fill.ForeColor.RGB = vbRed Next sh
For i= loops are used in situations when you know exactly how many times you need to go through the loop They are useful for traversing through cells or for making computations
Dim i As Integer For i = Start To End Step inc statement(s) Optional Next i
Dim myrow As Integer For myrow = 3 To 17 Cells(myrow, 2) = "Hello" Next myrow
Dim myrow As Integer For myrow = 17 To 3 Step -1 Cells(myrow, 2) = "Hello" Next myrow
Dim i As Integer Dim sum As Integer For i = 1 To 100 sum = sum + i Next i
You can exit any For" loop at any time using the Exit For statement This kicks you out of the loop and it moves the execution to the next statement just outside the loop
