How-To Use Loops in Excel (with Example Script)

A loop is a sequence of instructions that is continually repeated until a certain condition is reached. Typically, a particular process is done, such as getting an item of data and changing it, and then some condition is checked such as whether a counter has reached a prescribed number. If it hasn't, the next instruction in the sequence is an instruction to return to the first instruction in the sequence and repeat the sequence. If the condition has been reached, the next instruction "falls through" to the next sequential instruction or branches outside the loop. A typical case would be to go through a number of lines from an Excel spreadsheet, a data table, or a web browser table to perform some task for each record.

Loops are one of the most essential and important features available in Foxtrot. It allows the whole or parts of a script to repeat itself for either:

• An exact number of times
  • When the "Exact times" option is selected, an additional option will appear, allowing you to control how many times the loop will repeat before stopping.
• Forever
  • The "Forever" option does not have any additional settings. A loop that is configured to run Forever will only stop when instructed to do so with the use of an Exit Loop Action.
• According to a List
  • When the "List" option is selected, you will be prompted to select the desired List. You may also specify the Start and Stop boundaries of the Loop. To use the List option, your Project must first contain a List, which can either be a list of data retrieved from a web table, an Excel document, a list of files and folders, a custom list, or something similar.

In this article, we will take a closer look at how you can loop in Excel. It is important to keep in mind that there are almost an infinite number of different ways of working through data in Excel - especially because data in Excel can vary in such a significant degree. Also, there is a great difference between opening or loading data from Excel in order to loop through or in other ways work with the data outside of Excel, for example, open load a list of companies from Excel and look them up on a website. In such cases, you would use either Open List or Get List to load the data from Excel into Foxtrot and then loop through the data, so you are not actually looping in Excel, you are simply grabbing the data from Excel into a list in Foxtrot in order to loop through it. Of course, you can also use lists inside of Excel to transfer or in other ways work with larger portions of data at the same time.

For more details on how to work with lists, read this article:

• How-To Work With Lists

This article will focus mostly on the "Exact times" and "Forever" loop types.

Looping in Excel

In the attached file (find at the end of the article), you can get an example of how you can structure loops in Excel. You can download the script and try to run it. Remember to save the Foxtrot Project File to the same location as your other projects, typically in this destination: C:\ProgramData\Foxtrot Suite

This example shows some of the different typical loops relevant to build in Foxtrot - and how to use the "Exact times" and "Forever" loop types. Whether the loop involves Excel, another type of file, a web browser or some other application type, the overall structure should look the same. The key to making successful loops is determining the number of times to perform the loop - or turned around - when to exit the loop.

In the attached project, first we simply create a new blank workbook and populate some random data in column A. There are a few important things to notice right away:

• In order to loop a random number of times, we use the loop type "Exact times" and one of the available "Random" tokens to set the loop times to be anything between 1 and 9.
• Inside the loop, we use the Send Value action to write a random value between 0 and 99999. Notice how we determine the row number: We use the "Add" formula to use the row current-loop-number + 1 as the column contains a header. Therefore, first loop must write in row 2, second loop in row 3, etc.

After populating some random data in column A, it is time to loop through the data in column A. First, we use the first of two approaches by utilizing the "Exact times" loop type. With this approach, the logic is:

• Create a variable and store the total number of rows to loop through. To retrieve the total number if rows to loop through, we use the Get Info action to get the last row number of the workbook. Of course, how you retrieve the appropriate number of rows to loop through might vary depending on the dataset.
• The loop itself will use "Exact times" based on the variable with the value of the total number of rows to loop through.
• Inside the loop, we create a variable to determine the row number to work with for the current loop. In this case, it is simply the current loop number + 1.
• Just for the sake of the example, we write a simple Excel formula in the column B using the current row number.

The second approach is ultimately doing the same thing but with a different loop logic. Here, we use the "Forever" loop:

• Because the "Forever" loop does not offer a current loop number token, we create a variable before the loop to keep track of the current row number in Excel. Because our starting point for the loop is the second row in Excel, we set the starting value of the variable to be 2.
  • Notice how we do not actually check how many times we are about to loop before the loop start like we did in the first loop. Instead, we will simply loop until some condition is met that indicates that we should stop looping.
• The first step inside the loop is to actually evaluate whether we should exit the loop. Of course, the condition that indicates whether we should exit the loop depends on the situation and the dataset, but it is very important to define a solid condition, otherwise, you might risk to exit the loop either too early or too late. In our case, we simply say that if the current cell is blank, we know that we have reached the end of the dataset, therefore, we should exit the loop. But, what if there is a chance that there are blank cells inside the dataset? Then this would not be a valid condition as you would exit the loop too early, meaning you would not handle all the data. Therefore, it is very important to keep that in mind when using an approach like this.
• At the end of the loop, we make sure to increase the value of the variable storing the current loop number with one in order to make sure that we will handle the next row in the next loop.