VBA For Loop

You might be wondering what a for loop is and how it can help you. Well, a 'for' loop is a concept in programming that involves repeating a particular command a specified number of times. It is used to ensure that the instructions are carried out at least once. By using the For…Next statement in your macro, you can easily loop through a defined range of cells. 

What is a for loop?

A for loop is a built-in function in Excel that falls under the category of Logical function and repeats a conditional statement for a known number of times.

For example, if you wanted your VBA program to check the buy/sell transactions for the first twenty entries, we loop from 1 to that number. Based on such a loop in our code, we can highlight the cell values with different colors to distinguish the buy and sell transactions.

For Loop Syntax

The syntax for using For….Next loop in your VBA code is:

For [counter = start value to end value] [Step increment]
(conditional statements)
Next [counter]

where,

• counter = the variable assigned for the loop
• start value = the starting value for the loop counter 
• end value  = the end value for the loop counter
• step increment (optional) = This parameter increases the counter value every time the loop is completed once. By default, the assigned value for step increment is 1 but can be any number ranging from positive to negative values.
• conditional statements = the statements that are executed inside the loop.
  

Basic For Loop

Suppose a teacher in a classroom of 25 students needs to determine which students have scored marks below 60 in English and Maths so that they can join the extra lectures scheduled for next month. 

To accomplish this, the teacher can use the for loop along with the conditional statements to determine which students should be notified about the extra lectures.

The dataset for the marks scored by the students is as below:

The F column will display the status as 'Yes' or 'No' based on the condition that even if the student has marks below 60 in either subject, he will join the extra lectures next month.

Let's check the VBA code in depth to understand what conditions can be incorporated while using the For loop to determine the status in the F column.

When you run the VBA code from the Macros in the Developer tab, you will get the following result: 

In this code, we set up two conditions: if marks for either English or Math are less than 60, the students are eligible for extra lectures. This is represented by the code Cells(n, 4) < 60 Or Cells(n, 5) < 60. The counter for the loop starts with 3 and ends with 27 since our dataset begins from the third row(excluding the headers). Since step increment is not mentioned, it assumes the default values of 1, i.e., n+1, in our loop counter.

We have tried to differentiate our results in column F using a simple Cells code (n, 6).Interior.Color = RGB(255,255,0) if we get 'Yes' as an answer. This makes it easier to visually differentiate between the IF…else statement results obtained from our VBA code.

For Loop using incremental step

Using the for loop by incorporating incremental steps is a great way to skip unnecessary parts of the dataset in your spreadsheet. 

For example, you might often receive Excel spreadsheets where each row containing data is separated by a blank row. The reason behind this could be to protect the integrity of the data set to avoid it getting mixed up together, but it just makes it difficult for us to analyze the data. For example, below is the similar data representing the credit scores for the borrowers looking to take loans from a bank.

Since it is the bank's policy only to lend loans if the borrower's credit score is greater than 720, the information about the Co Borrower is deemed unnecessary in your analysis to determine the approval of loans. In this case, using incremental steps in our counter is a great way to skip the Co Borrower's information in our dataset.
The VBA code to achieve this is:

When you run this code, you will get the result in your spreadsheet as below: 

We see that the value of our counter follows an equation of n = n + 2, where n is the starting value of the counter. So the next values of the loop counter are 5, 7, 9, 11, 13, etc.

The step counter, thus, proves beneficial in skipping consistent unnecessary discrepancies in your dataset. 

Similarly, a negative step counter can be used to loop through our data. For example, if our step counter were equal to minus 2 with our start value as 3, our next value in the loop would have been 1. This wouldn't be quite helpful, right? That's why we change our start/end value when using a negative step counter. Let's see an example of a negative step counter in the next section to understand its application better. 

Negative incremental step counter in for loops

It is usually challenging to determine how many rows and columns are in the workbook when working on large datasets. Counting the rows manually and setting the start/stop value in our for loop can become tedious. In such cases, a combination of the last row/last column and positive/negative incremental step counter works great on our data.

Assume that you have Tesla Inc (NASDAQ: TSLA) OHLC data for the past year. 

You need to analyze the price trend based on historical data. This includes checking whether the previous day's opening and closing price are greater or less than the next day's opening and closing price to generate buy/sell signals. This example is in no way any fundamental or technical analysis on Tesla stock but is used to demonstrate how negative step counters can be used in a for loop. 

Our spreadsheet looks as below, extending up to 250 rows. In addition, we have added additional headers after column G, which will be used in our Tesla Inc stock analysis. 

We have written the below VBA code that incorporates the use of nested If-Else statements along with for loop. The general idea with the code is to check our opening/closing price flags in H and I column respectively, and if they return as "TRUE", then move onto the next column, i.e., to determine whether the closing price is greater than the opening price in column J.

Finally we check whether the day's volume is greater than the previous volume or not in column K. A value represented by "TRUE" in J and K column, will generate "Buy" signal in our spreadsheet dataset in column L if any of the cell value returns as "FALSE" the loop breaks and takes a new value in the counter. Hence, a "Buy" signal will be generated if all the preceding values are "TRUE".

A trader/investor believes that if the opening/closing price today is greater than yesterday, the stock is in an uptrend. The stock is bullish when the stock closes above its opening price(Column J assumption). Similarly, if there is a surge in stock volume from the previous day, it shows that the stock is bullish and people are looking to invest/trade in the stock. A combination of these parameters is used to build the VBA code, which is as below:

If you are new to If...Else statements or nested If statements, check out our article on the same here!

We get the following result in our spreadsheet when we run the macro. 

Based on our analysis in the spreadsheet, we see that the criteria to generate "BUY" signals were only met 36 times from the total of 253 trading days prices. Further examination of the data based on "BUY" signals shows that on 18 occasions out of 36, the price for Tesla Inc stock closed higher than the previous day closed, indicating a 50% accuracy. Not bad for an Excel model based on VBA code, right?

When you remove the filter from your dataset, this is how your spreadsheet will look like this:

 For each statement

A for…each loop is similar to the for loop, with the only difference being the step counter does not exist in the former. Therefore, the for each loop must be executed for all the elements present in the group or the assigned array. Let's see an example as to how for each loop works differently than the for loop in the VBA code.

Let's say you work for an NGO that volunteers for different public services. This month you receive five applications, and you need to allocate each person to different sectors for volunteering. So this is what your spreadsheet data looks like:

Since we know the range of our data, i.e., F3:F7, we used the for each loop in our VBA code which is as below:

When you run the code, the code iterates through a collection of ranges from F3:F7, and the conditional statements check in which age category the person falls. Then, based on the fulfillment of conditions, the code offsets one cell to the right and gives the result in the G column as to where the person should be stationed for volunteering.

Exit For statement

If you intend to exit the for loops immediately, you can use the Exit For statement in your code. This is usually helpful when the condition is met for the for loops. Pretty stupid to keep waiting for the loop to end if we already get our expected result, right? 

The Exit for statement is used along with the if statement and can be executed if the condition is met. For example, you might need to find a specific value in your database. Then, you can use a for loop and exit it once the cell is found.

Suppose you make an algorithmic trading software based on the historical data of stocks. Then, you generate buy-signals based on the VBA code we had written previously in the article here.

For our algorithm software to learn when to buy stock, it must accept correct data. Else it will cause our software to generate inaccuracies.

Our final result from the VBA code was as below, which we have copied to a separate spreadsheet:

The inputs here that the software needs to learn are the values in columns H, I, J, and K. If the value is "TRUE", our software will work correctly. However, if there are any "FALSE" values in any of the columns, the software gives poor results in terms of trade accuracies (the goal here is to get 50% accuracy or higher as per our analysis after running the code).

Let's assume that the conditions of our code are perfectly met, but still, the result we get is "FALSE" in some of the cells. Therefore, we need to find those cells and change them to "TRUE"  manually. To detect this, we have written the VBA code as below:

When you run the macro, the result that you will get in your spreadsheet will look like this:

You will observe that the macro highlights all the values that appear first. The code exits the loop after it finds the value and moves onto the next column due to the nested if statement. Next, all these values will be then changed to "TRUE", and the macro will be rerun to check if any other "FALSE" values exist in the dataset before it is loaded into the algorithmic software.

In this code, we also have a new Goto Statement that directly lets us jump to the required line of code in our VBA script.

You can use this code using the Goto statement followed by the label. This label is later repeated, followed by the colon, which helps to jump to anywhere in the code. The name for the label could be anything, just as long as they match.

Practical Example #1

Suppose you have created different sheets in your workbook and need to rename them all in one go. For example, assume all sheets have a financial analysis done for different companies. The code we have written to rename the n number of sheets in the workbook is as follows.

When you run the code, the name of the spreadsheets in your workbook will look like this:

If you intend to be more specific with the name of the spreadsheets, you can use the for each loop in the code. For example, suppose you are working on the financial analysis of Tesla Inc, Microsoft, Apple Inc, NetFlix, and Meta. Then, you can create an array for these companies and run a for each loop in the code which looks as below:

After running the code, you will get the following result:

Practical Example #2

Empty cells can be really annoying while working on the data. Firstly, it tends to break the data causing irregularities in it or causing discrepancies in the analysis. As a result, inserting the value as N/A or just 0 (zero) helps fill those gaps.

Let's say you have this stock data and need to fill in those empty cells in the dataset. Of course, you could also delete those cells, but that would change the dynamics of your data, i.e., either shift the data towards the left or in the up direction.

The VBA code that you can use for this is:

You will find that for any empty cells, its value would have been updated to zero. 

Such empty cells interfere while making dynamic macros, such as looping up to the last row or columns. Therefore, it is necessary to make references with the slightest chance of being empty. For our table, we have referenced the headers while looping through the column, while for the rows, it is the dates in the A column. Adding a column and serial numbers is also a great trick to use as a reference in the code.

In case of the loop breaks, you can always change the start/end value along with the step counter to test the validity of your code. For example, changing the for code to -

For j = last column to 1 Step -1 or For i = last row to 2 Step -1

If you need more examples to look at, check out our article on If..Else Statement here that incorporates both for loops and conditional statements!

More on Excel

To continue your journey towards becoming an Excel wizard, check out these additional helpful WSO resources.

• Excel keyboard shortcuts for Windows and Mac
• Advanced Excel formulas you must know
• Best Excel courses
• Developer tab
• INDEX MATCH formula