VBA Loops

Spread the love

Loop – it is a sequential process of instructions that is continually repeated until a defined condition is found.
Loops type – For next , for each , do while , do until , while wend

For Next Loop

It executes the code from defined start num to end num value

Note – Option to jump value by using “Step” (+ or -)

Copy below code
Sub For_Next_loopTrail()

Dim x as integer
For x = 1 to 20 ‘ Step 2 ‘do uncomment step 2 also step -.10 can be used
Range(“a” & x).value = x

              Next
End Sub

Note – Also variable can be assigned for 1 and 30 if requirement for dynamically use

 

For Each Loop

It executes every object within a set of objects like ( ranges, worksheets etc.)

Copy below code and try

‘Performed on each Range in Selected Range
Sub EachRng ()

Dim rng as range , x as long
Dim SetRange as range

x = 1
Set SetRange = Range(“a1:c15”) ‘Specify worksheet if run on particular sheet

For each rng in SetRange
Rng.value = X
x = x +1
Next rng

End sub

 

 

Do  Loop

it works until a defined statement is True or False
Note: sometimes it becomes infinite loop if given statement is not logical
Do While Loop , Do Until Loop

DoStarts with
While Keeps working if given conditions true if not then exit
UntilFinds until given condition is true, if matched then exit
Continue DoOptional. Even iteration continue next do loop
Exit DoOptional. Exit from Do
Loopterminates a loop or exit

 

Do Until Loop 

Copy below list and Paste it in B1 cell in sheet
Name
VIKARAM
SUNIL
SACHIN
SURESH
MANISH
PRAVEEN
VINOD
HARISH

 

Copy below code and try

Sub DO_LOOP()

X = 2
Do Until Range(“B” & X).Value = “MANISH”

MsgBox Range(“B” & X).Value
X = X + 1

Loop

End Sub

 

Do While Loop 

Copy below list of number and Paste it in B1 cell in sheet

Numbers
2
1
2
5
7
10
2
1

Copy below code and try

Sub DO_WHILE()

X = 2

Do While Range(“B” & X).Value < 6

      MsgBox Range(“B” & X).Value
X = X + 1

Loop

End Sub