Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024
Difficulty: Easy
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

Looping in Excel VBA enables users to loop through a given range of cells. This is one of the most efficient programming techniques.

Single Loop

This kind of looping technique provides the user to loop through a one-dimensional range of cells.

Using the command button user must add the following lines of code:

Dim i As Integer
For i = 1 To 5
    Cells(i, 1).Value = 50
Next i

Result: 

Explanation: The code written between For and Next will be executed five times. At the intersection of row 1 and column 1, for i=1, Excel VBA will enter the value 50. The same looping process will continue for the intersection of row 2 and column 1.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Double Loop

This kind of looping technique provides the user to loop through a two-dimensional range of cells.

Using the command button user must add the following lines of code:

Dim i As Integer, j As Integer
For i = 1 To 5
    For j = 1 To 3
        Cells(i, j).Value = 50
    Next j
Next i

Result:

Explanation:  At the intersection of row 1 and column 1, for i=1 and j=1, Excel VBA will enter the value 50. Similarly, At the intersection of row 1 and column 2, for i=1 and j=2, Excel VBA will enter the value 50. The same process will repeat for row 1 and column 3.

Triple Loop

This kind of looping technique provides the user to loop through a two-dimensional range of cells on multiple worksheets.

Using the command button user must add the following lines of code:

Dim c As Integer, i As Integer, j As Integer
For c = 1 To 2
    For i = 1 To 4
        For j = 1 To 2
            Worksheets(c).Cells(i, j).Value = 50
        Next j
    Next i
Next c

Explanation: As it is clear, the above lines of code only mark the difference of adding one more loop.

Do While Loop

This looping technique continues till the code written after Do While turns out to be true.

Using the command button user must add the following lines of code:

Dim i As Integer
i = 1
Do While i < 4
    Cells(i, 1).Value = 10
    i = i + 1
Loop

Result:

Explanation: As long as the condition holds to be true, Excel VBA enters the value 10.

FAQs

1. Name different types of looping techniques in Excel VBA.

The different looping techniques are Single loop, Double loop, Triple loop, and Do While loop.

2. What is the use of a single loop?

This looping technique allows the user to loop through a one-dimensional range of cells.

3.  What is the use of a double loop?

This looping technique allows the user to loop through a two-dimensional range of cells.

4.  What is the use of a triple loop?

This loop allows the user to loop through a two-dimensional range of cells in multiple worksheets.

5.  What is the use of the Do While loop?

This looping technique continues till the code written after Do While turns out to be true.

Key Takeaways

Congratulations on finishing the blog!! After reading this blog, you will grasp the concept of the Loops in Excel VBA.

If you are preparing yourself for the top tech companies, don't worry. Coding Ninjas has your back. Visit this link for a well-defined and structured material that will help you provide access to knowledge in every domain.

Topics covered
1.
Introduction
2.
Single Loop
3.
Double Loop
4.
Triple Loop
5.
Do While Loop
6.
FAQs
7.
Key Takeaways