excel vba – simple loop

Using Excel VBA to perform tasks using a loop

There are a number of ways of writing loops in Excel VBA, but I like to use the ‘Do Until’ method. The basic structure is shown below:
Do Until 'whatever criteria you set, see below'
'code for the action you want to perform'
Loop

The code will repeat the process until the criteria has been met. A simple use would be to go down a list of data until it finds a cell with a certain value. Each time the Loop runs it will select the next cell down using the Offset command, until the selected cell contains ‘ABC’.

Range("A1").Select
Do Until Activecell.Value="ABC"
ActiveCell.Offset(1,0).Select
Loop

You could also use a Variable (Counter in this example) so the Loop runs a certain number of times, updating the counter on each loop until it reaches the limit set.

Dim Counter as Integer
Counter=1
Do Until Counter=3
'code for the action you want to perform'
Counter=Counter+1
Loop

‘Code for the action you want to perform’

Loops can contain code to perform a number of actions when automating reports. You can perform calculations, insert formulas, perform formatting and pretty much anything else you need to do multiple times.

Leave a Reply

Your email address will not be published. Required fields are marked *