VBA- Loops

In VBA, The For Loop is used when you can determine the number of times it will be run or number of iterations are already know in advance. For example if you want to repeat something ten or twenty times.

Syntax

For <variable> = <start value> to <end value> Step <number>

Set of lines goes here….

Next <variable>

So let’s take an example where I have to print first 5 natural numbers using For Loop

Sub Test()

Dim x As Integer

For x = 1 To 3 Step 1

MsgBox x

Next i

End Sub

The above code can be modified and could be write in short way as below-

Sub Test()

Dim x As Integer

For x = 1 To 3

MsgBox x

Next

End Sub

Please Note, This will also produce the same output as you can notice we have remove Step (As by default For Loop takes 1 step) and also remove x after Next because here we have only Single loop to execute which is understood by VBA Complier.

Example: To print first 10 Natural Numbers in Excel Sheet

Sub Test()

Dim x As Integer

For x = 1 To 10

Cells(x,1).value=x

Next

End Sub

After executing this code you will get a series of numbers from 1 to 10 in Excel Sheet from A1 to A10 in the sequence. As mentioned in Loop Cells(x,1) tell compiler to increase 1 in row and in column 1 i.e. A1, A2, A3…..A10.

 Example: To create worksheets Automatically (Named 1May, 2May, 3May…..10May) using For Loop

Sub Table()

Dim i As Integer

For i = 1 To 10

sheets.Add(After:=Worksheets(1)).Name = i & “May”

Next i

End Sub

Leave a Reply

Your email address will not be published.

Enjoy the tutorial? Please spread the word :)

Follow by Email
Facebook
Facebook
Google+2k
Google+
http://hirdeshbhardwaj.com/2017/04/05/vba-loops
YouTube39
YouTube