Vba for each loop in Excel

The "For Each" loop is used to iterate through a collection in Arrays.

There is not any condition clause as in "For Loop"

This statement is most useful when iterating through unknown number of items in a Collection.

Syntax

  1. For Each in
  2. Next

The following example illustrates the use of "For Each "

  1. Sub for_each()
  2. Dim str() As String
  3. str = Split("Welcome @ to @ VBA!!", "@")
  4. For Each s In str
  5. MsgBox s
  6. Next s
  7. End Sub

The above example splits a string(str) into an Array and is displayed in a Message box with "For Each" loop

Note that, The number of stings in the Array will be unknown, when it has to be got from the user during runtime.

excel vba for each loop

The Result of the above code is as below

excel vba for each loop

excel vba for each loop

excel vba for each loop

 

You can find similar Excel Questions and Answer hereunder

1) Here an explanation about the options in button control in Excel VBA

2) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

3) Here a explanation about the global seek function in VBA. Goal Seek is another tool under What If analysis that does a unique function as Scenario Manager.

4) How to do a nested loop in VBA or a loop in a loop

5) How to hide and unhide rows and columns in Excel

6) How can I get the last non-zero value in a row?

7) How to debug a macro in Excel VBA

8) How do I have proper (Capitalize the first letter in each word of a text) text in cells ?

9) How to do loops in VBA with the while loop

10) How to do loops in VBA with the for next loop

 

Here the previous and next chapter