Explain about For - - - Next Loops

For Loops having fixed Loop(number of times) structure
Number of Iterations are fixed

Syntax of For - - - Next Loop:

Counter: It a numeric variable., i.e an integer. Also termed as Loop variable

Start:It is numeric expression which denotes starting value of Counter Variable

End: It is numeric expression which denotes ending value of Counter Variable

Step Size:Increament in Counter upon completion of each and every iteration...

Statements: which is termed as Body of loop., it consists of one or more than one statements....

(i)It denotes end of Loop structure. When we mentioned more than one loop for recognization purpose only we can mention "counter" key word subsequent to NEXT. Ex: Next i, Next j ....

(ii)Every for must end with NEXT

Last Cell - XL Down Method

While running the loop defining max value plays vital role. What we have know how many times loop has to run. Max value to loop defines number of times that loop has to execute

Through Xl Down method we can define end value

1)From top to Bottom

2)From Bottom to top

3):Loop end value based on CURRENT REGION

Private Sub CommandButton1_Click()
Dim s As Integer
s = Sheets.Count
For i = 1 To s
Sheets(i).Activate
'selection of current region from range E3., tc copy
ActiveSheet.Range("E3").CurrentRegion.Select
Selection.Copy
Next
End Sub

4)For Loop Max value = Sheets.count

In all the sheets we can print "sriguranjani" in range of "A1:B11"

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Activate
ActiveSheet.Range("a1:B11").Value = "Sriguranjani"
ActiveSheet.Range("a1:B11").EntireColumn.AutoFit
Next
End Sub

Download The Workbook

5)For Loop Max value = Number

Print numbers upto 101 in Column A & B

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 101
Cells(i, 1).Value = i
Range("B" & i).Value = i
Next
End Sub

Download The Workbook

6)Loop Max\end value = SELECTION

Prior to execusion of loop select required range of cells in COLUMN A
Those numbers will display in COLUMN B

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = selection
For i = 1 To rng.Cells.Count
Cells(i, 2) = rng.Cells(i, 1).Value
Next
End Sub

Download The Workbook

7)Loop Max\end value = INPUTBOX

Private Sub CommandButton1_Click()
Dim r As Range
Set r = Application.InputBox("select value", Type:=8)
For i = 1 To r.Cells.Count
Cells(i, 2).Value = r.Cells(i, 1).Value
Next
End Sub

Download The Workbook

7)Selection.rows.count

for i = 1 to selection.rows.count
for i = 1 to selection.columns.count

8)Loop Runs Till the End value

Private Sub CommandButton1_Click()
'Columns(1).Select
For i = 1 To Columns(1).Rows.Count
If Cells(i, 1) = "" Then
Exit For
ElseIf Cells(i, 1) <> "" Then
Cells(i, 2).Value = Cells(i, 1)
End If
Next
End Sub

Download The Workbook

Explain about For Each - - - Next Loop structure

What is the difference between "For - - -Next" and "For Each - - - Next" Loop

In For -----Next Loop we will mention COUNTER, which signifies number of times the program required to run

For Each -----Next Loop doesn't require Loop counter

It runs based on number of elements that group consists of (or)

It runs based on once for each item in the group

Syntax for: "For Each - - - Next" Loop:

Element represnets to: it is a variable to Iterate all the items in the Group

A Group is either collection of Object or an Array

For Each - - - - Next Loop always executes based on number of elements that Group\Object consists of
Ex: Work book having 10 sheets, then loop rotates 10 times