How to Make For Loop in Excel Using Formulas [VBA or Macros]

If you want to know how to make for loop in Excel using formulas, then you are in the right place. In this article, we are going to show you step by steps to create a loop in Excel VBA which will let you loop over a range of cells by using simple code lines.

Excel VBA allows you to automate processes in Excel by creating so-called macros, which are meant for macroprogramming. There are four kinds of FOR loops available in Excel. These are Single Loop, Double Loop, Triple Loop, and Do While Loop. If you have previous experience in coding in Excel VBA, then you are blessed. But if you don’t, then worry not.

We have shown an easy step-by-step tutorial on how you can make for loop in Excel using formulas. But first, we have to know how to turn on the Developer ribbon, create a Macro, and add a button.

So, let’s get started.

Pre-Requirements

As we already told before, there are some pre-requirements that we have to do before we move on to the main topic.

How to Turn On The Developer Tab

By default, the Developer tab is hidden from the ribbon. Because not all Excel users are well aware of programming and so they might do something bad with it. So, turning on the Developer Tab/Ribbon is the very first step that we have to do.

Follow the below steps to turn on this Developer tab.

1. Right-click anywhere on the ribbon and then choose to Customize the Ribbon from the drop-down menu.
2. On the right-hand side of the dialog box, pick Main Tabs from the Customize the Ribbon section’s drop-down menu (if it’s not previously chosen).
3. Now make sure you tick the option labeled “Developer”.
4. Confirm your selection by clicking OK.

The Developer tab can now be found next to the View tab on the ribbon bar.

How to Place A Command Button

Although placing the Command Button is not necessary before we move on to the main topic, it would still be better to know previously. To add one, follow the steps below carefully.

1. Open a Workbook in Excel and click on the Developer Tab.
2. Click on the Insert button in the Controls section.
3. On the ActiveX Controls group, click on the very first button, which is the Command Button.

4. Now, on the worksheet, drag your mouse in a cell to place the button.

Note: You can also insert checkboxes instead of buttons from the ActiveX Control Group.

Assigning a Macro To The Command Button

Now, once we have placed a command button in the worksheet, it’s time to assign a macro or the programming code to it. To set a macro (a code line or many codes ) to the command button, follow the procedures outlined below.

1. After placing the Command button in the Workbook, make sure that the button is set as the Design Mode.
2. Right-click on the CommandButton1 and hit the View Code option from the drop-down menu. Afterward, the Visual Basic Editor will show up.

3. Now click on between Private Sub CommandButton1_Click() and End Sub and enter the below code:

Range (“A1”).Value = “Hello”

Note:: It would be good if you type the code above manually instead of copying the code and pasting it. Because, for some reason, the quotation mark can be changed, and the code won’t work. Also, for some reason, if you don’t see the names Sheet1 (Sheet1) and ThisWorkbook on the left-hand side, or if you have closed this window, you can make it reappear by clicking on the View tab and hitting on the Project Explorer option. Or you can also press Ctrl+R on your keyboard to do the same.

4. Close the Visual Basic Editor and turn off Design Mode.
5. Now click on that command button, and the Hello word on range A1 will appear.

How to Make For Loop In Excel Using Formulas

Now that we have done all the pre-requirements, it’s time to move on to the main topic of this article. As we already know, there are four types of FOR loops available in Excel VBA. Now, we are going to show and briefly describe each of these loops. So, sit tight and read the rest of the article carefully.

Single Loop

In order to build a single loop, the FOR…NEXT statement is used, which is the easiest version of the FOR loop. Allowing you to rerun VBA code an unlimited number of times will be possible with this function. You could use a single loop for cycling across a one-dimensional range of cells, which is useful in Excel.

So, follow the below steps to use a Single Loop:

1. Open a blank Excel workbook, and click on the Developer option on the Ribbon bar.
2. Click on the Insert button in the Controls section and on the ActiveX Controls group, click on the very first button, which is the Command Button.
3. Now, on the worksheet, drag your mouse in a cell to place the button.
4. Now make sure the Design Mode option is enabled, and right-click on the command button and hit View Code from the drop-down menu.
5. The VBA editor will now appear. Write the below code in between the Private Sub CommandButton1_Click() and End Sub.

The Code is:

Dim i As Integer

For i = 1 To 6

Cells(i, 1).Value = 100

Next i

6. Close the VBA editor once you are done writing the code.
7. Click on the Command button and see the magic. The 100 texts will appear in the cell from A1 to A6. (Make sure the Design Mode is disabled).

Why is this happening? The code lines within For and Next will be performed a total of six times. The number 100 is entered into the cell that is at the intersection of row 1 and column 1 when the value i = 1 is used.

When Excel VBA hits Next I, it increments I by one and returns to the For statement, which is where it all began. Using Excel VBA, For i = 2, Excel puts the value 100 into the cell at the intersection of row 2 and column 1.

However, as a general rule, it is recommended that while you will be writing the code between the words For and Next, you always use the Tab button. Making your code more readable is a good thing!

Double Loop

Now we have done making the Single Loop in Excel VBA; it’s time to create another one. In order to loop across a two-dimensional range of cells, you may use a double looping method. Now we are going to create a Double Loop. So, follow the below steps carefully:

1. Open a blank Excel workbook, and click on the Developer option on the Ribbon bar.
2. Click on the Insert button in the Controls section and on the ActiveX Controls group, click on the very first button, which is the Command Button.
3. Now, on the worksheet, drag your mouse in a cell to place the button.
4. Now make sure the Design Mode option is enabled, and right-click on the command button and hit View Code from the drop-down menu.
5. The VBA editor will now appear. Write the below code in between the Private Sub CommandButton1_Click() and End Sub.

The Code is:

Dim i As Integer, j As Integer

For i = 1 To 6

For j = 1 To 2

Cells(i, j).Value = 100

Next j

Next i

6. Close the VBA editor once you are done writing the code.
7. Click on the Command button and see the result. The 100 texts will appear in the cell from A1 to A6 and B1 to B6. (Make sure the Design Mode is disabled).

So, why did this happen? When i and j are 1 in the code, For i = 1 and j = 1, Excel VBA puts the number 100 into the cell that intersects row 1 and column 1. When Excel VBA hits the Next j command, it increments the value of j by one and returns to the For j statement. The cell at the junction of row 1 and column 2 is filled with the value 100 by Excel VBA when i = 1 and j = 2, respectively.

Following that, Excel VBA skips Next j since j only goes from 1 to 2 in the numbering system. When Excel VBA hits the Next i statement, it increments i by one and returns to the For i statement to continue. In the case of For i = 2 and j = 1, Excel VBA places the value 100 in the cell at the junction of row 2 and column 1, and so on. It continues.

Triple Loop

The advantage of using a triple loop is that it may be used to loop across two-dimensional ranges on several Excel worksheets at the same time. The next part will show you how to design a triple FOR loop in Microsoft Excel using the steps outlined before:

1. Open a blank Excel workbook, and place a command button on your Workbook by following the previous steps.
2. Now make sure the Design Mode option is enabled, and right-click on the command button and hit View Code from the drop-down menu.
3. The VBA editor will now appear. Write the below code in between the Private Sub CommandButton1_Click() and End Sub.

The Code is:

Dim c As Integer, i As Integer, j As Integer

For c = 1 To 3

For i = 1 To 6

For j = 1 To 2

Worksheets(c).Cells(i, j).Value = 100

Next j

Next i

Next c

4. Close the VBA editor once you are done writing the code.
5. Click on the Command button and see the result. The 100 texts will appear in the cell from A1 to A6 and B1 to B6 in all of your other worksheets. (Make sure the Design Mode is disabled).

To clarify, the only difference between the code for the double loop and this code is that we have included one further loop and placed Worksheets(c) in front of Cells to acquire the two-dimensional range on the very first sheet for c = 1, the second sheet for c = 2, and the third sheet for c = 3.

We hope you understand.

Do While Loop

In addition to the For Next loop, there are many more loops available in Excel VBA. Consider the Do While Loop, for example. Programs that are written between the statements Do While and Loop will be continued as long as the statement after Do While is true. To see this in action, follow the below steps:

1. Open a blank Excel workbook, and write some numbers in column A.
2. Place a Command Button on your Workbook by following the previous steps.
3. Now make sure the Design Mode option is enabled, and right-click on the command button and hit View Code from the drop-down menu.
4. The VBA editor will now appear. Write the below code in between the Private Sub CommandButton1_Click() and End Sub.

The Code is:

Dim i As Integer

i = 1

Do While Cells(i, 1).Value <> “”

Cells(i, 2).Value = Cells(i, 1).Value + 10

i = i + 1

Loop

5. Close the VBA editor once you are done writing the code.
6. Click on the Command button and see the result. The numbers you have written in column A will be increased by 10. (Make sure the Design Mode is disabled).

If Cells(i, 1).Value is not blank; Excel VBA puts the number in the cell at row i and column 2 which is ten greater than the value in the cell at row i and column 1. Since Cells(7, 1). Value is empty; Excel VBA shuts down when I equal 7 in the formula. Using this method, you can easily loop over any amount of rows in a spreadsheet.

Conclusion

Loops in Excel help a lot to save time. In this guide, we tried to show four different types of For loops that you can try. We hope you succeed to make for loop in Excel using formulas.