VBA basic syntax For Each structure, what are the features, precautions

The previous chapter shared the meaning of the Do While loop structure and how to use it, today this chapter will share with you the basic syntax of VBA For Each... Next features, instructions for use, etc.

First, let's take a look at the specific meaning of the For Each structure:

For Each element variable in combination (can be a collection or array)

The block of statements to execute

Exit For

Next element variable

Please see the screenshot for a specific explanation:

VBA basic syntax For Each structure, what are the features, precautions

Note: For Each..... Next is much more flexible than other loop structures, and it doesn't require any loop judgments, especially when looping arrays or collections. Another point is that you can't change the value of the array when looping through the array, and for an array that already has values, you can only modify the properties of the elements (that is, the example given earlier, changing the font color, etc.).

Let's take a simple example to see the effect together:

Next is a cell with a lot of names in column A (regardless of the name duplication issue)

All we need to do is use this newly learned loop structure to fetch the data and output it to a table.

Here's an explanation of the code:

Code 1:

Sub fe()

Dim i As Integer, k As Integer, j As Variant, b As Integer

Dim shuzu() As Variant

b = WorksheetFunction.CountA(Range("A:A"))

ReDim shuzu(1 To b) As Variant

For k = 1 To b

shuzu(k) = Cells(k, 1)

i = 1

For Each j In shuzu

Cells(i, 2) = j

i = i + 1

End Sub

Another common example (it doesn't make any sense, it's mainly about understanding how the For each loop structure is used):

Using For Each..... Next Write a program that outputs integers from 1 to 20 to cells A1 to A20.

Code 2:

Sub sh()

Dim k As Range, j As Integer

j = 1

For Each k In Range("A1:A20")

k.Value = j

j = d + 1

Note: The output will be in the cell of your active table, (the activity refers to the table you are now opening and manipulating), you can try where you switch to that sheet, and the result will be output.

Today first share here, like please pay attention, thank you!

Continue sharing tomorrow