Home > Topics > excel > body text

What is the usage of for loop statement in Excel VBA?

coldplay.xixi
Release: 2020-06-28 11:07:08
Original
11671 people have browsed it

Usage of for loop statement in vb: 1. [for..next] statement, the structure judged by i is [for i=initial value to end value step step value]; 2. [for each. .next] statement is an object variable, and its structure is [For each object variable in object collection].

What is the usage of for loop statement in Excel VBA?

Usage of for loop statement in vb:

1. There are two commonly used for loop statements, One is the for…next structure; the other is the For each…next structure. These two structures are mainly used when looping through multiple data. So below I will introduce the specific usage of the two loop structures respectively. First, you need to open the VBA editor

What is the usage of for loop statement in Excel VBA?

2, for...next Structure

This structure is generally like this:

for i=initial value to end value step step value

……

next

To explain in detail, the initial value refers to the value at the beginning of the loop, and the end value refers to the value at the end of the loop. In essence, it is a judgment that i is worthy. If the i value is between the starting value and the end value, then enter Loop statement, and then the i value will automatically add a step value every time it runs to the next statement. The loop will not end until the i value exceeds the range from the initial value to the end value. It should be noted here that the "step value" is often omitted. If omitted, the default value of the step is 1. The following will use a small example to specifically illustrate the usage of this structure:

Example: Output the numbers 1 to 10 at a time in the sheet1 worksheet and display them in the first column.

Program:

Sub 循环语句()
Dim i As Integer
For i = 1 To 10
  Cells(i, 1) = i
Next
End Sub
Copy after login

What is the usage of for loop statement in Excel VBA?

For loop structure with a step size of 2

Let’s take a look at a sample program here

Program:

Sub 循环语句()
Dim i As Integer
For i = 1 To 10 Step 2
    Cells(i, 1) = i
Next
End Sub
Copy after login

What is the usage of for loop statement in Excel VBA?

For loop structure with a step size of -1

Here we look at the same thing when the step value is - 1, what will the procedure be like?

Look at the example program below:

Sub 循环语句()
Dim i As Integer
For i = 10 To 1 Step -1
    Cells(i, 1) = i
Next
End Sub
Copy after login

What is the usage of for loop statement in Excel VBA?

##3、

for each...next Structure

Specific structure:

For each object variable in object collection

……

next

Explain in detail, here we see something different from the previous structure The above structure is mainly a numerical variable, while this structure is an object variable. What does that mean? Each...in refers to traversing each object one side at a time in this object collection. In the same way, after executing next, the object automatically points to the next one. Specifically, let's take a look at an example below

Example: Assign values ​​to all cells in a data range, starting from 1.

Analysis: Obviously the data area is a collection of objects, and the cells are the objects in this collection

Program:

Sub 循环语句()
Dim i As Integer
For Each c In Range("a1:c5")
  i = i + 1
  c.Value = i
Next
End Sub
Copy after login

What is the usage of for loop statement in Excel VBA?

Loop statements are very flexible to use. The above only introduces the basic usage of two types of loop statements. Loop statements can be applied. This needs to be used flexibly based on the specific situation!

Below we use multi-loop statement nesting to implement the multiplication formula:

Program:

Sub 循环语句()
Dim i, j As Integer
For i = 1 To 9
  For j = 1 To i
    Cells(i, j) = i & "*" & j & "=" & i * j
 Next
Next
End Sub
Copy after login

What is the usage of for loop statement in Excel VBA?

Recommended tutorial: "

excel basic tutorial

The above is the detailed content of What is the usage of for loop statement in Excel VBA?. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template