Looping with Excel VBA

A Looping Construct in Excel

Looping in Excel is the process of repeating a piece of code a set number of times in VBA.  It is handy if you want to perform the same action a number of times and takes some of the manual work out of manipulating a file. Like most things in Excel there are many ways to perform looping constructs and everyone seems to have their own preference.  I certainly have a looping preference. The two main types of Excel Loops in VBA are the For Next Loop and the Do loops.

Loop and the Do Loop

The following is a simple example of a For Next loop.  The VBA code will cycle through Cells A1 to A10 and place a value in each of the cells starting from 1 and going to 10.

The above coding will write the numbers 1 to 10 in Cells A1:A10.  Loops like the one above are very handy if you are dealing in small data sets.  As such looping through larger ranges should be done sparingly.  See VBA Golden Rules (Rule 7) for more information.  In my opinion there are far more efficient ways to get a result, for example the above code can be produced with the following non looping alternative.  The VBA code will insert numbers from 1 to 10 in A1 to A10.
 
Sub OnetoTen() 'A Fomula applied in Excel VBA which will do the same as the above loop.
    [a1:a10]=[if(row(a1:a10),roundup(row(a1:a10),0))]
End Sub

Actually the procedure above is a great example of why thinking about what you are doing before getting into a looping construct is important.  To be completely honest, if you were to do the above you could do it with formula but this is used as an example of how loops can be avoided in VBA.   

If you wanted to take the Excel VBA coding a step further for instance type the Number 1 10 times, number 2 a further 10 times all the ways down to say 100.  So you would have numbers 1 to 10 taking up 100 rows.

Sub OnetoaHungy() 'A Fomula applied in Excel VBA which will do the same as the above loop.
    [a1:a100]=[if(row(a1:a100),roundup(row(a1:a100)/10,0))]
End Sub

The first looping construct would need to add a loop within a loop to achieve the above. While the VBA code above only needs to change 1 number. The difference in speed between the two methods becomes more apparent the longer the list of numbers you require. Because the VBA code above performs one action once and the looping construct will need to perform possibly thousands of actions depending on the size of your Excel list.  The lesson with loops is to use them if you can establish there is no built in alternative in Excel.

Double Loop with VBA

Performing a double loop in Excel vba is quite simple, it is about putting a loop inside a loop.  The VBA code run through the first loop (first column) and that tends to trigger a second loop.  For example, you might have 2 columns and you want to check if the category for each column is a book.  A loop within a loop will allow you to check both categories.

Option Explicit

Sub DoubleLoop()
Dim i As Integer
Dim j As Integer

For i = 1 To 2
    For j = 2 To 6
        If Cells(j, i) = "Book" Then Cells(j, 3) = "Yes"
    Next j
Next i

End Sub

If either cells in the first two columns from A2 to B6 contains the word Book then Excel will put the word Yes into column 3.

Tripple Loop with VBA

The same concept as a double loop with VBA can be used for a tripple loop with VBA.  You could check the same condition over multiple worksheets.  Let's say we have 3 sheets which we want to check this condition on.

Option Explicit

Sub TrippleLoop()
Dim i As Integer
Dim j As Integer
Dim n as Integer

For i = 1 To 2
    For j = 2 To 6
         For n = 1 to 3
              If sheets(n).Cells(j, i) = "Book" Then sheets(n).Cells(j, 3) = "Yes"
         Next n
    Next j
Next i

End Sub

The above will loop through all 3 sheets and check the cells contents from A2 to B6 for the word book and put yes in the corresponding row.  This makes the assumption that the workbooks are set up the same.  So you can perform a tripple loop quite easily with a little forethought.