Sponsored Link

There was an error in this gadget

Sponsored Link

Thursday, April 30, 2009

VBA, For Next loop

For next is looping method when you are definite about number of values you want to scan/test. However, you will have to use IF then to test the values. Lets take a look at very basic example of For Next loop and then we will move at little complex examples.

Syntax:

For counter = startvalue to endvalue
    Operation you want to perform
Next counter
Next counter increase the value to loop till counter reaches end value

Here you want to just enter random numbers starting from active cell to next 10 row using VBA/Macro code

For Next loop Example 1

Sub Fornextloop1()
Dim i As Integer

'We will use i as counter
For i = 0 To 9
    ActiveCell.Offset(i, 0) = WorksheetFunction.RandBetween(25, 250)
Next i

End Sub

Note: Code     ActiveCell.Offset(i, 0) = WorksheetFunction.RandBetween(25, 250) may not work with MS Office 2003 or previous version. Please replace the following code with

ActiveCell.Offset(i, 0) = WorksheetFunction.Rand(25, 250)

Similary, if you want to list of 10 values of actual and 10 values of target lying in set of adjacent cells and you want to find out whether actual have exceeded the target or not.

For Next loop Example 2

Sub Fornextloop2()
Dim i As Integer
'We will use i as counter
For i = 0 To 9
    If ActiveCell.Offset(i, 0) >= ActiveCell.Offset(i, 1) Then
        ActiveCell.Offset(i, 2) = "MET"
    Else
        ActiveCell.Offset(i, 2) = "NOT MET"
    End If
Next i

End Sub

Here in the above examples we are taking increment of 1 only. In case if you want to have increment of more than 1 than you can take look at example where I have illustrated Example 1 but you only want to put even numbers. Like 2, 4, 6.... 20

For Next loop Example 3

Sub Fornextloop3()
Dim i As Integer

'We will use i as counter
For i = 0 To 20 Step 2
    ActiveCell.Offset(i / 2, 0) = i
Next i
End Sub

Step 2 makes the counter incremented by 2 each time. Also, i / 2 under offset ensures that each value in inserted in row. Only i will insert values in alternate cells.

You may think is it necessary always to increase value of counter can't we decrease the value of counter. Yes, we can do that as well. Next example illustrate the same. Now, we want to scan all the rows starting from 40th row and move to 1st row. Also, we want to highlight values above certain threshold (Here I have take 30 as threshold).

For Next loop Example 4

Sub Fornextloop4()
Dim i As Integer
'We will use i as counter
ActiveSheet.Cells(1, 1).Select
For i = 39 To 1 Step -1
     If ActiveCell.Offset(i, 0) > 30 Then
        ActiveCell.Offset(i, 0).Interior.ColorIndex = 3
    End If
Next i
End Sub

Step -1 decreases the value of i by 1.

Next example will shows us how to run nested For Next loop which is of extremely use full when you want to move across rows and columns at a time. I am using the example similar as previous one.

For Next loop Example 5

Sub Fornextloop5()
Dim i As Integer
Dim j As Integer
'We will use i as counter
ActiveSheet.Cells(1, 1).Select
For i = 11 To 1 Step -1
    For j = 11 To 1 Step -1
        If ActiveCell.Offset(i, j) > 30 Then
            ActiveCell.Offset(i, j).Interior.ColorIndex = 3
        End If
    Next j
Next i
End Sub

I am sure this post will help you in applying For Next Loop. In case if you find difficult to use in your MS Excel Sheet. I would suggest you download file with Example. Press Alt + F11 to view code and press Alt+ F8 to run the code.

For Next Example

Please do let me know your comments about my attempt to help beginners with For Next loop. Also, you can subscribe via email

We assure you knowledge, not SPAM!

6 comments:

mohsin said...

Hello
A good post with wonderful ideas

www.supirscience.blogspot.com

www.mnewsalert.blogspot.com

bilal said...

This is a very good post. Please check the site and get information. Please click on the given.
www.hadithislamic.blogspot.com/

This is a very good post. Please click on the given.
www.businessintroduction.blogspot.com

This is a very good post. Please click on the given.
www.learnenglishskills.blogspot.com

This is a very good post. Please click on the given.
www.learncomputerapplication.blogspot.com

bilal said...

This is a very good post. Please check the site and get information. Please click on the given.
www.hadithislamic.blogspot.com/

This is a very good post. Please click on the given.
www.businessintroduction.blogspot.com

This is a very good post. Please click on the given.
www.learnenglishskills.blogspot.com

This is a very good post. Please click on the given.
www.learncomputerapplication.blogspot.com

kundan said...

Sub Fornextloop1()
Dim i As Integer
'We will use i as counter
For i = 0 To 9
ActiveCell.Offset(i, 0) = WorksheetFunction.RandBetween(25, 250)
Next i

End Sub
Hi Sarfaraz,

macro in 1st example is not working. showing error at below line. Tried both codes.

ActiveCell.Offset(i, 0) = WorksheetFunction.RandBetween(25, 250)

Again can you please elaborate on this example.

thanks
kundandalvi1@rediffmail.com

Sarfaraz Ahmed said...

Kindly let me know if you have tried downloading example file.

Mack said...

I got a solution from Sarfaraz which was an instant solution and really helped me in my busy schedule using Excel.

I asked on how to remove the password (assigned in Office 2003)from an excel file after installing the office 2007 and got the solution really quick.

Sarfaraz - Pls give the solution online on your website. It will help others as well.

Thanks again for all your help time and again.

Mahesh Subramanian
+91-9962453531

Post a Comment