For Next Loop in Excel 2013 using VBA
We can loop within given range by using For Next loop in VBA. In this article we discuss about how to use For Next loop in VBA.
In this example we discuss about how to copy the data of Column A to the Column B using For Next Loop.
Open Microsoft Office Excel 2013 => Select the DEVELOPER menu => Add ActiveX Command Button and Caption it as “Copy”
Double click on the button and add below code for click event.
Private Sub CommandButton1_Click()
rowCnt = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To rowCnt
Cells(i, 2).Value = Cells(i, 1).Value
Next i
End Sub
As shown above first we are getting the valid data rows count based on Column A to the rowCnt. Then copying the Cell A values to Cell B Values by looping A Cell rows using For Next statement.
Before click on Copy button, the output shown as below.
After Click on the Copy button Cell A values copied to Cell B and the Output displays as shown below.
We can exit from the For Next loop by using Exit For statement before completing the loop as shown below.
Private Sub CommandButton1_Click()
rowCnt = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To rowCnt
If i = 2 Then
Exit For
End If
Cells(i, 2).Value = Cells(i, 1).Value
Next i
End Sub
As shown above For Next loop is going to terminate when i is equal to 2 using Exit For statement.
ForNext.zip (24.3KB)