VBA LOOPS

If you are writing a VBA program and you want to execute the same task multiple times then you can do this by using VBA for loop.

For Loops

For counter = start To end [Step stepcount]

   [statement 1]

   [statement 2]

   ....

   [statement n]

   [Exit For]

   [statement 11]

   [statement 22]

   ....

   [statement n]

Next

Example 1

‘Display message box  1 to 10 times

Private Sub example1()

   Dim a As Integer

   a = 10

  

   For i = 0 To a

      MsgBox "The value is i is : " & i

   Next

End Sub

Sub exaple2()

'Fills cells A1:A56 with values of X by looping' --- Comment

'Increase value of X by 1 in each loop' --- Comment

Dim X As Integer

    For X = 1 To 56

        Range("A" & X).Value = X

    Next X

End Sub

Sub Example3()

' Fills cells B1:B56 with the 56 background colours' --- Comment

Dim X As Integer

    For X = 1 To 56

        Range("B" & X).Select

        With Selection.Interior

            .ColorIndex = X

            .Pattern = xlSolid

        End With

    Next X

End Sub

CONTINUE