Excel VBA to Use For Loop with Two Variables

A Quick Overview of For Loops (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 the VBA Editor in Excel

• Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
• Press Insert and select 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

Example 1 – Using Two Variables to Compile Financial Modelling

Let’s assume a scenario given the dataset where we 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.

• Use the following VBA code in your Module.

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

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

Example 2 – Excel VBA Nested For Loop for Creating a Multiplication Table

Consider a multiplication table. If you look at the table you can see the number is progressing along with the cell of each row or column. We can apply a Nested For Loop to advance through rows and columns.

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

• Use 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

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.

You’ll need to have a worksheet active for this code to work properly.

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

Example 3 – Calculating Revenue Using a VBA For Loop with Two Variables in Excel

Case 3.1 – Iterate Through Rows

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

• Use 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.

Case 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 the same.

• Use 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.

Example 4 – Utilizing a For Each Loop to Calculate Revenue

Case 4.1 – Calculating Revenue after Selecting the Quantity Range

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

• Use 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.

Case 4.2 – Calculating Revenue Based on a Particular Quantity

• 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 added 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.

Example 5 – For Loop Within an Array to Calculate Revenue

We can plug the value in the For Loop to calculate the Revenue. Here’s the code.

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 a 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 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.

• Here’s 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

• 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

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, the code 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 insert 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 a VBA For Loop with Two Variables in Excel

We’ll make code that allows users to input values in cell via input boxes, looping through rows.

• Insert 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

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

Frequently Asked Questions

How many loops exist 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 a VBA for loop?

• 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 can have a maximum value of 2,147,483,647.
• You cannot change the loop control variables dynamically in a 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.
• 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.
• A 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.

Download the Practice Workbook

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