VBA – 3. Loops

FOR LOOP

The Microsoft Excel FOR…NEXT statement is used to create a FOR loop so that you can execute VBA code a fixed number of times.

It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

Syntax

The syntax to create a FOR Loop using the FOR…NEXT statement in Microsoft Excel is:

FOR counter = start TO end [Step increment] {…statements…}
NEXT [counter];

Example:

Read all values in column A using Loop with a msgbox. So msgbox will display A, B,C.. all values in column.

loops

Dim i As Integer

For i = 2 To 11
 MsgBox Range("A" & i).Value
 'OR
 MsgBox Cells(i, "A").Value

Next i

IF YOU WANT TO READ VALUES USING INTERVAL OF 2 then

For i = 2 To 11 step 2
 MsgBox Range("A" & i).Value
 'OR
 MsgBox Cells(i, "A").Value

Next i

IF YOU WANT TO READ VALUES USING INTERVAL OF -2 then you can read from bottom

For i = 11 To 1 step -2
 MsgBox Range("A" & i).Value
 'OR
 MsgBox Cells(i, "A").Value

Next i
Exiting out of for loop, to exit out of for loop once any condition is met, use exit for

For i = 2 To 11 step 2

     if i=5 then

     exit for

      end if
Next i

IF CONDITIONS WITH FOR LOOP

put grades based on conditions in right table using loop with if conditions

ifloop

for i=2 to lastRow
  
  if Range("B" & i).value<33 then
msgbox "fail"

elseif range("B" & i).value>33 and Range("B" & i).value<=50 then
msgbox "avg"

elseif...

else..

end if

WHILE LOOP

WHILE condition
   {...statements...}
END;
dim i as integer

i=5

while i<5

msgbox i

i=i+1

wend

Do While Loop

i = 0
Do While i <= 100
 If i > 10 Then
 Exit Do ' Loop Exits if i>10
 End If
 MsgBox ("The Value of i is : " & i)
 i = i + 2
Loop

Nested For loops (it can be used to read rows and columns from a range)

for i=1 to lastRow

   for j=1 to lastCol

         msgbox cells(i,j)  'Display the values of each cell row and column wise

           next j

next i

do Until loop

keep looping until we find India Project. Once india project is found it exits out of loop

dountil
i = 1

Do Until Range(“B” & i).Value = “India project”
i = i + 1
Loop

MsgBox “found” & Range(“B” & i).Value

For Each Loop

for each loop are used to loop with objects, eg ranges, worksheets, workbooks.

Example: loop through  cells A1 to A4 in Sheet, and show the value of cells from A1 to A4 in msgbox

dim c as range  ‘ we have to declare object type variable

for each c in range(“A1:A4”)

msgbox c.value

next c

Example: loop through  each sheet in current workbook and display sheet name in msgbox 

dim ws as workseheet ‘ we have to declare object type variable

for each ws  in  thisworkbook.sheets

msgbox ws.name

next c

Example: Hide sheets, sheet1, sheet2, sheet3 and again  read all sheets and if password is PASSWORD, then unhide them 

Dim sht As Variant
For Each sht In Array(“Sheet1”, “Sheet2”, “Sheet3”)
Sheets(sht).Visible = xlVeryHidden
Next sht
End Sub
Sub Show_Sheet()
Dim pword As String
Dim sht As Variant
pword = Application.InputBox(“Enter password to view Sheets 1,2,3”, “Password Required”)
If pword = “PASSWORD” Then
For Each sht In Array(“Sheet1”, “Sheet2”, “Sheet3”)
Sheets(sht).Visible = True
Next sht
End If

 

Leave a Reply