Excel VBA to Use For Loop with Two Variables

Get FREE Advanced Excel Exercises with Solutions!

Like other programming languages, VBA allows individuals to automate tasks that would otherwise be repetitive and time-consuming. You can use it to analyze data which is particularly important in fields such as finance, healthcare, and marketing, where data analysis is critical for making informed decisions. In this article, we will learn how to use Excel VBA For loop two variables.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


A Quick Overview of For Loop (For Next and For Each)

A For Loop or For Next is a type of control structure that enables you to repeat a block of code a specific number of times. When you need to run a set of instructions repeatedly while making small changes between iterations, this loop comes in handy. For instance, you could iterate through a group of cells in an Excel spreadsheet using a For loop and do calculations in each cell along the way.

A typical syntax for using For Loop can be constructed as follows:

For counter_variable = starting_value To ending_value [Step step_value]
    ' statements to be executed
Next [counter_variable]

The variable that will be used to count the loop iterations in this syntax is designated as counter variable. The counter variable‘s starting value is its beginning value, and the ending value is the point at which the loop will come to an end. The step value will determine the increment or decrement of the counter variable with each loop of iteration. Step value will take its default value of 1 if not specified.

On the other hand, you can use the For Each loop to execute a block of code a fixed number of times as well, but it is more flexible and you can use it when you don’t know the exact number of times the loop needs to execute. The syntax for a For Each loop is as follows:

For Each element In collection
    ' statements to be executed
Next [element]

In this syntax, collection is a set of connected things that you want to loop through, and element is a variable that denotes each item in the collection. Between the For Each and Next statements, there will be a set of instructions that will be performed as part of the loop. For each element in the collection, these assertions will be carried out.

Read More: How to Use Excel VBA Nested For Loop


How to Launch VBA Editor in Excel

To open the VBA code editor in Excel you can utilize the keyboard shortcut. Let’s see the process.

  • Press Alt + F11 to open your Microsoft Visual Basic.
  • Then press Insert > Module to open a blank module.

opening of VBA editor in Excel worksheet

  • You can write your code in the code editor/Module.

Vba Editor interface


Excel VBA For Loop with Two Variables: 5 Suitable Examples

As businesses have grown and the volume of data they need to manage has increased, traditional methods of calculating financial information by hand or using calculators have become less practical. In response, many businesses have turned to computer programming to automate financial calculations, making it easier to manage and analyze large datasets. In this article, we will explore the use of VBA For Loops with two variables in Excel VBA to streamline financial calculations.


1. Using Two Variables to Compile Financial Modelling

Input data and output data for financial modeling

To make you familiarize yourself with VBA For Loop, Let’s assume a scenario given the dataset above that you want to create a model that calculates the future value of an investment over time, based on an initial investment amount and a fixed interest rate. If you calculate the return value each year, the task will be boring and tiresome. Instead of doing that, you can use VBA For Loop and make a code of your own to accomplish the task.

  • To proceed with the task, write the following VBA code in your Module. You can also write the code in the corresponding sheet.

Vba code for financial modeling

Sub Financial_Modelling()
Dim initial_investment As Double
Dim interest_rate As Double
Dim years As Integer
Dim future_value As Double
Dim current_value As Double
Dim previous_value As Double
initial_investment = Cells(4, 3).Value
interest_rate = Cells(5, 3).Value
years = Cells(6, 3).Value
previous_value = initial_investment
For i = 1 To years
    ' Calculate the value of the investment at the current time period
    current_value = previous_value * (1 + interest_rate)
    ' Update the previous_value variable for the next iteration of the loop
    previous_value = current_value
    ' Print out the value of the investment
    Cells(4 + i, 5).Value = "Year " & i
    Cells(4 + i, 6).Value = "$" & current_value
Next i
End Sub

Code Breakdown

  • In the first section of the code, The code declares some variables using the “Dim” keyword: initial_investment, interest_rate, years, future_value, current_value, and previous_value. Using Cells.value function, the code assigns values to the first three variables based on the values in specific cells.
  • The code then enters a For Loop that runs from i = 1 to the number of years specified in the Excel worksheet.
  • Inside the For Loop, the code calculates the current value of the investment at the current period by multiplying the previous value by (1 + interest_rate). Then updates the previous_value variable to be equal to the current_value for the next iteration of the loop.
  • The calculated value will return in each of the loops and write the output value in the sheet. Later, the counter variable will proceed with the steps for the later years as well.

  • Now Run your code by pressing F5 and see the output as given below.

2. Excel VBA Nested For Loop for Creating a Multiplication Table

Targeted output using nested for loop

Consider a table multiplication as given above. If you look at the table you can see the number is progressing along with the cell of each row or column, You can apply a Nested For Loop to accomplish the task and have a significant idea about Nested For Loop by doing so.

A Nested For Loop in VBA is a loop structure where one loop is placed inside another loop. It is used to iterate over multiple sets of data in a systematic way

  • To begin with this method, open your VBA editor by following the steps described above.
  • Write the following code in your module box.

vba nested for loop code of multiplication table

Sub ForLoop_MultiplicationTable() 
Dim rowValue As Integer, colValue As Integer 
 For rowValue = 1 To 9     
  For colValue = 1 To 9         
   Cells(rowValue + 3, colValue + 1).Value = rowValue * colValue     
  Next colValue
 Next rowValue 
End Sub

In this example, the outer loop iterates over the numbers 1 to 9, and for each iteration, the inner loop iterates over the numbers 1 to 9 and calculates the product of the two numbers. The Cells(rowValue+ 3, colValue + 1).Value = rowValue * colValue statement inside the inner loop assigns the product to the cell in the i-th row and j-th column of the active worksheet.

Note that, you’ll need to have a worksheet active for this code to work properly.

  • Now Run your code by pressing F5 and see the output as given below.

3. Calculating Revenue Using VBA For Loop with Two Variables in Excel

Each of the tasks we are dealing with in our everyday life is different. But when it comes to working with a repetitive task, you have to rely on For Loop. There are multiple ways to utilize the For Loop depending on your dataset type and the goal you want to accomplish. For Example, your input data reside in some cells of 5th row or column E. You might already sense that you have to take each cell value of the given row or column and apply the cell value to your code. Here the following section, we will learn how to do that.


3.1. Iterate Through Rows

Revenue calculation using for loop iterating through rows

From the dataset, we will take the Quantity and Price per Unit value. As those values reside in columns E and F, therefore we have to make a loop that iterates through rows.

  • Write the following code in your VBA Editor

Vba code of for loop iterating through rows

Sub Calculate_Sales_Revenue()
    ' Set range of two variables quantity and Price per unit respectively
Set Quantity = Range("E5:E9")
Set Price = Range("F5:F9")
'Create a additional heading for Revenue Column
Quantity.Cells(0, 3).Value = "Revenue"
    ' Initiate a for loop to calculate the revenue
For i = 1 To Quantity.Rows.Count
    revenue = Quantity.Cells(i, 1).Value * Price.Cells(i, 1).Value
    Quantity.Cells(i, 3).Value = "$" & revenue
Next i
End Sub

Code Breakdown

  • The code is initiated with a sub procedure called “Calculate_Sales_Revenue”.
  • Set Quantity = Range(“E5:E9”) and Set Price = Range(“F5:F9”): This line creates two new Range object called “Quantity” and “Price” that refers to the cells E5 to E9 and F5 to F9 on the current worksheet.
  • For i = 1 To Quantity.Rows.Count: This line begins a for loop that will run once for each row in the Quantity range. The variable “i” is used as the loop counter and is initialized to 1. The Rows.Count property is used to determine the number of rows in the Quantity range.
  • revenue = Quantity.Cells(i, 1).Value * Price.Cells(i, 1).Value: This line calculates the revenue for the current row of data by multiplying the quantity (stored in cell i, 1 of the Quantity range) by the price per unit (stored in cell i, 1 of the Price range).

  • Run your code by pressing F5 and see the output as given below.

3.2. For Loop Through Columns

Revenue calculation using for loop iterating through columns

Upon using the previous method, you can also apply your VBA loop through each column of your dataset. The basic algorithm is as same as stated previously, however, you have to manipulate your cell argument to assign the task.

  • Write the following code in your VBA Editor

vba code for revenue calculation using for loop iterating through columns

Sub Calculate_Sales_Revenue()
    ' Set range of two variables quantity and Price per unit respectively
Set Quantity = Range("C7:G7")
Set Price = Range("C8:G8")
'Create a additional heading for Revenue Column
Quantity.Cells(2, 0).Value = "Revenue"
    ' Initiate a for loop to calculate the revenue
For i = 1 To Quantity.Columns.Count
    revenue = Quantity.Cells(1, i).Value * Price.Cells(1, i).Value
    Quantity.Cells(3, i).Value = "$" & revenue
Next i
End Sub

The given code will set Range(C7:G7) and Range(C8:G8) to store the Quantity and Price value. Later the for loop will calculate each of the Revenue values and return the data in the next cell.

  • To Run your code, press F5 and see the output as given below.

4. Utilizing For Each Loop to Calculate Revenue Calculation

Like the previous task, we can accomplish the same by using For Each Loop. This approach can save time and effort compared to manually calculating revenue. The approach also mitigates the code size and therefore can also provide greater flexibility and accuracy in handling complex datasets.


4.1. Calculating Revenue after Selecting Quantity Range

 Revenue calculation using for each loop

We can rewrite the previous code by introducing For Each Loop instead of giving the ending value of the For Loop.

  • Write the following code in your VBA Editor.

vba code for revenue calculation using for each loop 

Sub For_Each_Loop()
Set Quantity = Selection
For Each cell In Quantity
    Count = Count + 1
    revenue = cell.Value * Quantity .Cells(Count, 2).Value
    Quantity.Cells(Count, 3).Value = revenue
Next cell
End Sub

Set Quantity = Selection: This line creates a new Range object called “Quantity” that refers to the cells currently selected on the worksheet. The Set statement is used to assign the range object to the Quantity variable.

For Each cell In Quantity: This line begins a For Each loop that will iterate through each cell in the Quantity range. The variable “cell” is used to refer to the current cell in the loop.

  • Press  F5 to Run your code and see the output as given below.

4.2. Calculating Revenue Based on Particular Quantity

Revenue calculation using for each loop with if statement

Following the previous method, if we want to incorporate the IF statement in the given code to avoid calculating those revenues whose Quantity value is equal to or below 2, you can follow this code as stated below.

vba code for revenue calculation using for each loop with if statement

Sub For_Loop_With_IF_Statement()
Set Quantity = Selection
For Each cell In Quantity
Count = Count + 1
If cell.Value >= 4 Then
revenue = cell.Value * Quantity.Cells(Count, 2).Value
Quantity.Cells(Count, 3).Value = revenue
Else
End If
Next cell
End Sub

The code remains the same as the previous code, however, we add an IF statement to check whether the condition (greater than or equal to 4) is met or not.

  • Run your code by pressing F5 and see the output as given below.

5. For Loop Within An Array to Calculate Revenue

Revenue calculation using for loop within array

Now if you are thinking of using the same task alternatively you may detour to an array and plug the value in the For Loop to calculate the Revenue. The following example will give you a brief scenario regarding the concept,vba code for revenue calculation using for loop within array

Sub For_Loop_Within_Array()
Set rng1 = Range("E5:E9")
Set rng2 = Range("F5:F9")
Dim Quantity()
Dim Price()
ReDim Preserve Quantity(rng1.Rows.Count)
ReDim Preserve Price(rng2.Rows.Count)
For i = 1 To rng1.Rows.Count
Quantity(i) = rng1.Cells(i, 1).Value
Price(i) = rng2.Cells(i, 1).Value
Next i
For j = 1 To UBound(Quantity)
revenue = Quantity(j) * Price(j)
rng1.Cells(j, 3).Value = "$" & revenue
Next j
End Sub

Code BreakDown

  • The code is initiated by calling a sub-procedure named For_Loop_Within_Array. You can modify the name according to your preference if you wish to.
  • The next two lines set two ranges rng1, rng2 to call the cell value of the Quantity and Price per Unit column of the given dataset.
  • Dim Quantity() and Price() are two arrays without declaring any dimension size.
  • Redim Preserve declares the array size of both cases according to the rows.count value.
  • Then two For Loops will incorporate to store the cell value within the array and then will return those array values to calculate the Revenue.

  • Run your code (Press F5 or Run button) and see the output as given below.

I hope these examples demonstrated nicely to use a for loop with two variables in Excel VBA.

Read More: Excel VBA For Loop with Array


How to Convert Fahrenheit to Celsius with VBA For Loop in Excel

Fahrenheit to celsius conversion using for loop

In this section of our article, we will write a VBA macro in Excel to convert a range of Fahrenheit temperatures to Celsius using a For Loop. The macro will prompt the user to select a range of cells containing the Fahrenheit temperatures, and then it will loop through each cell in the range, convert the Fahrenheit temperature to Celsius, and write the result to a neighboring cell.

We will use the formula C = (F – 32) * 5/9 to perform the conversion, where C is the Celsius temperature and F is the Fahrenheit temperature. Let’s see the VBA code.

vba code for Fahrenheit to celsius conversion using for loop

Sub for_loop_Click()
'Example of Fahrenheit to Celsius with for loop
For x = 5 To 9
Cells(x, 3).Value = (5 / 9) * (Cells(x, 2).Value - 32)
Next x
End Sub
  • Now Run your code by pressing F5 and see the output as given below.

How to Iterate Through Rows Until Cell Is Empty with Loop in Excel VBA

Excel VBA Loop Through Rows Until Empty

It is frequently required to loop through rows until you reach an empty row or the end of the data while working with Excel data in VBA. This can come in handy for several purposes, including finding specific data, running calculations, and copying data to another location. In this example, we’ll create an Excel VBA macro that uses a For Loop to iterate through rows until we find an empty row. Users need to choose a range of cells that contain the data to be processed. Then it will loop through each row in the range, take some action, and will go on to the following row until it reaches an empty row.

  • Open the VBA Editor and Write down the following code in your Module box.

vba code for  Excel VBA Loop Through Rows Until Empty

Sub Loop_Through_Rows_Until_Blank()
Application.ScreenUpdating = False
NumRows = Range("B5", Range("B5").End(xlDown)).Rows.Count
Range("B5").Select
For x = 1 To NumRows
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub
  • Press  F5 to Run your code and see the output as given below.

Read More: Excel VBA: For Loop Skip to Next Iteration


How to Populate Two Columns Using  VBA For Loop with Two Variables in Excel

Populate Two Column Variables with For Loop in VBA Excel

In VBA, you can use a For Loop to populate two-column variables with data from an Excel worksheet. That technique may come as handy when you need to avoid putting your input value directly into the Excel sheet.

  • To do so write the following code in your module box.

vba code for Populating Two Column Variables with For Loop in VBA Excel

Sub Populate_Two_Columns()
num = InputBox("How many data you want to populate:")
'Input your value for both columns here
For i = 5 To num + 4
x = InputBox("Enter your Cells Value for Quantity Column:")
y = InputBox("Enter your Cells Value for Price Per Unit Column:")
Cells(i, 5).Value = x
Cells(i, 6).Value = y
Next i
End Sub
  • Now Run your code by pressing F5 and see the output as given below.

Frequently Asked Questions

  • How many loops in VBA Excel?

There are five loops in VBA Excel. Those are For Loop, While LoopDo While Loop, Do Until Loop, For Each Loop.

  • Can we use 2 variables in For Loop?

Yes, you can use multiple variables according to your work type. There is no such restriction on using variables inside the loop. However, if the question is asked for the loop control variable, then you are allowed to use two loop control variables. This is known as a “Nested For Loop“.

  • How do I use two for loops in VBA?

You can use two For Loops in VBA by nesting them within each other. This is called a “nested loop”. The basic syntax for a nested For Loop is as follows:

For i = 1 To n
For j = 1 To m
' Do something with i and j
Next j
Next i
  • How many parameters can a For Loop have?

A For Loop in VBA has three parameters: the loop control variable, the starting value, and the ending value. These parameters are specified within the parentheses of the For statement, separated by semicolons.

  • How many variables can be initialized in For Loop?

In VBA, you can initialize one variable in the For Loop statement. The initialization specifies the initial value of the loop control variable, which is used to control the loop.

  • Is there any limitation in VBA for loop 

Indeed, the VBA For Loop has some limits. Some of the primary restrictions are listed below:

1. The most iterations we can perform in a For Loop are 2,147,483,647. This is because the loop’s counter variable is of the Long data type, which may have a maximum value of 2,147,483,647.

2.  You cannot change the loop control variables dynamically in For Loop. This means that the number of iterations won’t change if the loop control variable’s value is changed while the loop is running.

3. As Strings and Dates are non-numeric data types so you cannot use it with the For Loop.  You must use a numeric data type, such as Integer, Long, or Double, for the loop control variable.

4. For Loop can only be used for linear iterations. If you need to loop through a non-linear range or a non-sequential set of values, you will need to use a different type of loop, such as a While Loop or a For Each Loop.

Overall, while the For Loop is a useful tool for automating repetitive tasks in Excel VBA, it has some limitations that you should be aware of when working with large data sets or complex tasks.


Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.


Conclusion

In conclusion, now you know the use of Excel VBA For loop using two variables.  It will carry out recurring operations based on combinations of the two variables. Upon reading the article, you may have an idea about VBA For Loop and be able to perform a wide range of activities later, from straightforward value assignments to intricate computations and data manipulations. However, If you have any queries, feel free to comment below and we will get back to you soon.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo