Excel application is commonly used by office workers, accountants,… In addition to basic calculation functions, Excel also allows you to use commands in VBA to solve math problems more easily. The tutorial below Taimienphi.vn share with you how to use F

For those who regularly use Excel software, the concept of functions in excel is no longer strange, but not everyone knows how to use advanced Developer features to program functions running in excel. When using this tool, you can do very quickly things that if done normally by hand, you would be extremely hard or even impossible to do.

Instructions on how to use For loop in VBA

Step 1 : To open the Developer feature in excel, go to File > Option:

Next, go to the Customize Ribbon tab and check the Developer section and click OK:

Step 2: In this article, I will guide you to do 2 things: Number from 1 to 10000 in column A starting from cell A5 and Print 10000 words “Hello World” in 1 Excel column which is column B starting from cell B5. To do this, go to the DEVELOPER tab on the toolbar and click on Visual Basic:

At this point, a VBA programming tool will open. You double-click on the sheet you are using to open the page that allows the statement to execute the For round:

Step 3 : You type in it the code of the For ring as shown:

Sub SoThuTu ()

Dim i As Integer
For i = 1 To 1000
Cells( i+4, 1) = 1
Next i

End Sub
 

Where:

– The Sub line SoThuTu() is to declare 1 function named SoThuTu.
– The Dim i As Integer line is to declare a variable i whose data type is an integer.
– For line i = 1 To 10000 is a loop declaration made with a run i value from 1 to 10000 .
– The line Cells ( i+4, 1) = 1 is the content of the For ring with the meaning: For each running i value, the i+4th excel cell of the 1st column (ie column A) will be assigned by the value of variable i.

Notice, in this example I print numbers from cell A5, not from cell A1, so within For I enter the code as i + 4. If you print from cell A1, you just need to type in the code Cells (i, 1) = 1 .

– The Next i line is a statement to allow the variable i to run to the next value.
– The End Sub line is the line to end the SoThuTu function after the function is done with the value i = 10000.

Step 4 : After typing the above code fully and correctly, click on the icon of the Save button as shown. And in the panel that appears, select Yes:

Step 5 : To run the function you just typed in, click on the green triangle button as shown in the picture:

In the new window that appears, select Run:

And now from cell A5 onwards has been numbered incrementally from 1 to 10000:

You can check if the value 10000 is fully printed by typing in the cell that moves the value A10004 to move it right to this cell. And here is the result that the function correctly printed 10000 values from 1 to 10000:

Step 6 : To print 10000 words “Hello World” in column B from cell B5, insert the following script into the SoThuTu function:

Here the line Cells(i+4, 2) = “Hello World” refers to: For each running i value , the i+ 4th excel cell of the 2nd column (i.e. column B) will be assigned to the text as Hello World.

After entering you the above code, click on the Save button as above and in the panel that appears you select Yes:

Next you click on the green triangle button as above to run the program:

In the new window that appears, select Run:

And now from cell B5 onwards is assigned to the text as Hello World:

You can check if the word Hello World is printed in full by typing in the box that moves the value B10004 to move immediately to this box. And here is the result that the function printed correctly:

https://thuthuat.taimienphi.vn/vong-lap-for-trong-vba-trong-excel-2285n.aspx
So you already know how to use For loop in VBA. With this For loop, you can solve many problems in your work. It is especially useful and will save you a lot of effort and time compared to regular hand-made.