Day 12 VBA Hero – Loops

When I first started this journey, I said that there are three things to learn when programming.

Variables, loops and arrays.

Welcome to loops.

What is a loop?

It is repeating the same task over and over again in a concide manner.

There are a few different kind of loops.

But frankly, if you only learn the two that I will share, you can literally do everything you need to in VBA.

In reality, you only need one, but the second one makes more sense in certains cases.

Now for an example, if I need to loop through all of the columns and concatenate two items from two different columns.

But of course, you can only concatenate them when the value in the cell is something specific like “LinkedIn”.

Otherwise, you will concatenate the values from another column.

Of course, this example you can do in Excel with simple formulas.

The point is to get used to the grammar of a loop.

Loop type 1: For Next

The for next loop is the most basic loop and one that is used the most often.

The grammar is pretty simple, it looks something like this.

For i = 1 to 20
‘Do something here
Next i

Where “i” is an integer variable that has previously been defined.

Sub TestingLoops()

Dim i as Integer

For i = 2 to 20
with Sheets(1)
If (.cells(i,3)) = "LinkedIn" then
.cells(i,6)= .cells(i,3) & .cells(i,4)
Else
.cells(i,6)= .cells(i,3) & .cells(i,5)
End if

Next i

End Sub

The above code has an error that I often do and thus wanted to share it.

I have not ended the With statement.

Thus I receive an error message that says : Complile Error Next with For.

Should you see this, it means that you have either not completed the loop, like forgetting the “Next i” line or you have not closed your With statement in a loop.

The correct code is this:

Sub TestingLoops()

Dim i As Integer

For i = 2 To 20
With Sheets("Sheet1")
If (.Cells(i, 3)) = "LinkedIn" Then
.Cells(i, 6) = .Cells(i, 3) & .Cells(i, 4)
Else
.Cells(i, 6) = .Cells(i, 3) & .Cells(i, 5)
End If
End With

Next i

End Sub

The homework this time will be to create a For Next loop and fill in the cells in a row between 2 and 100 with a concatenation of the number of the loop (the “i” value) and the words “-LinkedIn”

However if the value of the “i” is greater than 50, concatenate the “i’ value with the words “-Facebook”

I realize that I have not been putting up answers to the assignments.

I have found a way to do it and should be getting that done this weekend.

The next lesson will still be loops, but a different one.

For Each Next loop