VBA basics
16. Iterative processing (For Next)

Excel macro basics and applications, introduction to Excel VBA
last updated:09-01-2019

16. Iterative processing (For Next)

Some of this topic was processed by machine translation. Original

BA's For Next is a VBA syntax for repeating the same process.

There are several ways to iterate over VBA,
The most frequently used, the simplest and most convenient,
First of all, the VBA syntax for iteration to remember is For Next.

For Next is a statement to iterate,
Use this when you want to repeat the same process multiple times.
This is a mandatory description for automating processing with macros.

In the Excel table, there are many items arranged horizontally and data in the vertical.
All data in this table can be processed repeatedly with a short description using For Next.

Iterative processing is also called loop processing.
There are several ways to describe loop processing in macros,
The first thing to remember is For Next, which I will explain this time.

Let's check the basic syntax of For Next.

For Next statement

A flow control statement that repeats a series of statements a specified number of times.


For counter = start To end [Step step]
  [Exit For]

Next [counter]

counter Must be specified.
Specifies the numeric variable used for the counter.
You cannot specify variables that are contained in array variables or Boolean types.
start Must be specified.
Specifies the initial value of the argument counter.
end Must be specified.
Specifies the final value of the argument counter.
step optional.
Specify the value to be added to the argument counter each time the loop is repeated.
If the step argument is omitted, 1 is added to the counter argument each time the loop is repeated.
statements Optional.
A series of statements executed in a loop, written between For and Next.
The statement described here will be executed as many times as specified in the For ... Next statement.

This is an excerpt from the Excel help.
If you read this and understand it, you won't have a hard time and you won't see this site.
I will explain in detail.

First of all, a statement is an imperative statement that controls the macro behavior.
Let's remove the extra syntax and make it easier to read.
For variable = start value To end value
  ... Processing ...
This means that the variable repeats from the start value to the end value.

*Counter variable written after Next
Next [Counter Variable]
This counter variable is optional.
It's just a matter of appearance, so it doesn't matter whether you write or not.
Even though there is no writing on this site, there is no particular intention.

For Next Example

Let's deepen understanding with example sentences.
When 1 is inserted from A1 cell to A10 cell.

Sub practice1()
  Dim i
  For i = 1 To 10
    Cells(i, 1) = 1
  Next i
End Sub

The variable i is automatically increased from 1 to 10.
That means
Cells(1, 1) = 1
Cells(2, 1) = 1
Cells(10, 1) = 1
And in order, the variable i is executed with 1 ~ 10.
If you write a description line by line

Sub practice1()
  Dim i        'Declare variable i

  For i = 1 To 10   'Count up variable i from 1 to 10 one by one
    Cells(i, 1) = 1 'Put 1 in the cell in the i-th row of the value column
  Next i        'For range ends here
End Sub

Visually check For Next with step-in execution.

The method of executing a macro line by line is called step-in.
Coding and debugging
Debugging basics

VBA macro excel

VBA macro excel

With the input cursor in the range from Sub to End Sub,
Step into execution, press F8.

VBA macro excel

The yellow line means that this line will be executed.
Each time you press F8, the yellow line advances.

VBA macro excel

VBA macro excel

When you place the mouse cursor on a variable, the variable value pops up.

VBA macro excel

While pressing F8 one after another,
Check the condition of the sheet at that time.

VBA macro excel

Check the actual change of the variable and the change of the sheet.

When processing every other line

1st line, 3rd line, 5th line ...
This is how to write For Next when processing every other line.

Sub practice2()
  Dim i
  For i = 1 To 10 Step 2
    Cells(i, 1) = 1
  Next i
End Sub

Add a Step like this.

Step 2
This is the specification for every other line.
i increases by 2.
In other words, when you don't write this,
Step 1
It is to say.

You can specify a negative value for this step.
Step -1
Then, i will decrease by one.
If you want to process a cell row from the bottom or a sheet from the back.
This is very often used.

Macro re-introduction: 20th. Do the same thing for all sheets (For ~ Worksheets.Count)

Exit For

Exit For exits the For ... Next loop.

Before reaching the specified end value,
Use this when you want to end the For to Next loop processing.

Normal For ~ Next is used to repeat the specified number of times, so it is not good to use Exit For.
Use this when you want to break out of the loop before completing the specified number of times when certain conditions are met.

Sub practice3()
  Dim i
  For i = 1 To 10
    If Cells(i, 1) <> "" Then
      Exit For
    End If
    Cells(i, 1) = 1
End Sub

I want to repeat up to line 10,
If there is already a value in column A, the process ends there.

For ... Next Nest

When 1 is put in 10 rows and 10 columns.

Sub practice3()
  Dim i, j
  For i = 1 To 10
    For j = 1 To 10
      Cells(i, j) = 1
    Next j
  Next i
End Sub

Run and check.

You can write any number of For to Next nests,
If you nest too much, readability (readability) decreases.

Let's nest up to 3 levels.

Last word

Iterative processing is the basic programming principle.
If this is ambiguous, macros cannot be used in practice.
Please refer to the following articles to learn more.

Macro re-introduction

Articles with the same theme "VBA basics"

13. Constants and type declaration characters (Const)
14. Character concatenation and continuation line
15. Four arithmetic operations and annotations (comments)
16. Iterative processing (For Next)
17. Iterative processing (Do Loop)
18. Get last row (End, Rows.Count)
19. General practice question 1
20. Conditional branch (If)
21. Conditional branch (ElseIf)
22. Conditional branch (Select Case)
23. Message box (MsgBox function)

  • >
  • >
  • 16. Iterative processing (For Next)

  • If you find this site useful, please share and bookmark.

    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.

    If you find this site useful, please share and bookmark.
    To the bottom of the text