Excel VBA to Use For Loop with Two Variables

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.

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

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

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.

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

• 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

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.

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

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

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

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

3.2. For Loop 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

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

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.

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

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.

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

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,

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

• 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

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.

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

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.

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

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.

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 Loop,  Do 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

• Excel VBA: Running a Backwards For Loop
• How to Continue Excel VBA For Loop
• Excel VBA with For Loop Decrement
• Excel VBA: How to Exit a For Loop