How to Loop in Excel

By James T Wood

A standard formula in Excel cannot repeat more than once; it is a static calculation that only changes when other cell values change. However, you can use macros to execute looping commands based on Visual Basic for Applications code that you enter. Macros automate a series of actions so you can repeat them quickly and easily. Note that enabling macros can compromise the security of your computer, so only use looping macros when you are confident that the source of the Excel spreadsheet is secure.

Step 1

Launch Excel, open the spreadsheet where you want to use the looping command, and then press "Alt-F11" to open the Visual Basic Editor.

Step 2

Select the "Insert" menu, and then choose "UserForm." A UserForm window appears with a Controls Toolbox next to it. In the Controls Toolbox, select the "Command Button." Labels appear when you hover over the icons. Draw the Command Button in the UserForm window.

Step 3

Right-click on the Command Button you drew and select "View Code" to open the VBA code window for that button. Note that you can have multiple buttons per UserForm if you want to code and execute multiple loops.

Step 4

Enter the VBA code for the loop you want to perform between the "Private Sub" and "End Sub" lines in the code editor. For example, to fill a range of cells with data, you could enter the following code: Dim i As Integeri = 1Do While i < 5 Cells(i, 1).Value = 15 i = i + 1Loop

Step 5

Press "F5" to execute the loop. Alternatively, click the "Run" menu, and then select "Run Sub/UserForm."

Tips & Warnings

  • There are many more types of loops and functions you can run with VBA scripting (see Resources).