Understanding For-Each Loops in Excel VBA

Slide Note
Embed
Share

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.


Uploaded on Sep 21, 2024 | 0 Views


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


  1. CCE 170 - Brigham Young University Norm Jones

  2. 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

  3. For Each For i=1 To Do While Do Until

  4. 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

  5. Dim myitem As Variant For Each myitem In mycollection statement(s) Next myitem

  6. Dim mycell As Range For Each mycell In Range( B4:D20 ) If mycell.Value < 0 Then numneg = numneg + 1 End If Next mycell

  7. Dim sh As Shape For Each sh In Shapes sh.Fill.ForeColor.RGB = vbRed Next sh

  8. 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

  9. Dim i As Integer For i = Start To End Step inc statement(s) Optional Next i

  10. Dim myrow As Integer For myrow = 3 To 17 Cells(myrow, 2) = "Hello" Next myrow

  11. Dim myrow As Integer For myrow = 17 To 3 Step -1 Cells(myrow, 2) = "Hello" Next myrow

  12. Dim i As Integer Dim sum As Integer For i = 1 To 100 sum = sum + i Next i

  13. 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

More Related Content