Slide 2:
www.bluepecan.co.uk For next looping works by using a counter variable. The counter states the number of times a specific piece of code should be repeated.
Slide 3:
www.bluepecan.co.uk 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.
Slide 4:
www.bluepecan.co.uk 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
Slide 5:
www.bluepecan.co.uk 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
Slide 6:
www.bluepecan.co.uk 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
Slide 7:
www.bluepecan.co.uk 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:
Slide 8:
www.bluepecan.co.uk 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
Slide 9:
www.bluepecan.co.uk See this Excel training tutorial on the Blue Pecan website