Presentation Transcript
For-Next Looping in Excel VBA :www.bluepecan.co.uk For-Next Looping in Excel VBA Microsoft Office Training
Excel Training
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