Understanding For-Each Loops in Excel VBA
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
