Free Excel Training - For-Next Looping in Excel VBA

Microsoft Office Training > Excel Training > Free Computer Training > For-Next Looping in Excel VBA

Please feel free to use this content on your web site, but please include the links at the end

For-Next Looping in Excel VBA

For next looping works by using a counter variable.  The counter states the number of times a specific piece of code should be repeated.  The following macro produces a random number between 1 and 100.  The code includes a counter which will run the code 100 times offsetting each value by one row – in other words creating the values down a column.

Sub CountExample()
Dim Counter As Integer
For Counter = 1 To 100
ActiveCell.Value = Int (Rnd*100)
ActiveCell.Offset(1, 0).Select
Next Counter
End Sub

A step value can be included in a For-Next Loop.  The step value determines how the counter is incremented.  Change the active cell value to Counter to see the increment more clearly

Sub CountExample()
Dim Counter As Integer
For Counter = 1 To 100 Step 5
ActiveCell.Value = Counter
ActiveCell.Offset(1, 0).Select
Next Counter
End Sub

The macro might be more useful if the user can give their own counter and step values.  We will introduce a couple of input boxes for this purpose:

Sub CountExample2()
Dim Counter As Integer
Dim ToNum As Integer
Dim StepNum As Integer
ToNum = InputBox("What maximum value do you want?")
StepNum = InputBox("What increment do you want?")
For Counter = 1 To ToNum Step StepNum
ActiveCell.Value = Counter
ActiveCell.Offset(1, 0).Select
Next Counter
End Sub

Microsoft Office Training with Blue Pecan Computer Training. Tailored Microsoft Excel Training. Microsoft Excel Training Surrey