This tutorial will walk you through
a simple loop of columns in a row.
Here is a sneak peak of the output, which
we will generate from this basic loop macro in
Excel using Visual Basic for Applications.
Most Excel users know we can drag
across a couple of cells like this, in order
to generate a pattern of cell values.
For example from 1 to 10 like this.
Go to Visual Basic
for Applications, and insert a Module.
The main goal from this video is to show
Excel users how to compose a simple looping
macro based on a simple parameter.
More specifically, we will write a
looping macro to loop through
columns in a row in a spreadsheet.
In general, it is wise to add the Option
Explicit syntax at the top of the module.
The Option Explicit will it force us to
declare all of the variables in a
specific macro such as this one.
It will help to lessen the risk of
run-time errors whilst the macro is running.
Let's call the macro, loopColumnsRow
Remember to disable these Excel
properties, which pertain to the calculation,
event and screen properties.
Then we need to declare our object variables.
The i object will be declared as a long variable.
Whilst both Rng and startCell will be declared
as range object variables, as we need these
objects to denote cells or multiple cells in VBA.
The startCell range object will be declared
as the starting or active cell for this exercise,
whilst rng will be the selected range of cells.
We are now ready to add the key learning
component of this video; the basic looping
code to cycle across columns in a row
based on a simple criteria.
The For ... Next loop is one approach
to loop through a range of cells.
In this example, the loop will run based on the
i object variables of 1 to 10.
Given we want the first cell or startCell to be
the first cell to input a value, we want the
OFFSET syntax to be 0,0 for the first loop of i.
When i is 1.
Given we don't want to move the cursor down,
or up any rows nor columns for the first loop.
Hence the first loop of i, 1,
will generate the output value of 1.
The i variable can now move to 2, in order
for this loop to run again - the cursor will move
one column across and input the value of 2.
Our loop will continue to loop until the
i variable reaches 10.
It will then exit the loop, and place the cursor
back at the initial cell or startCell object variable.
The macro has finished running,
we can now re-enable those three Excel
properties that were previously disabled.
Our macro is now complete.
Let's review our macro,
before we run the macro in our spreadsheet.
Let's run the macro.
As we can see, the macro is successfully
looping across the columns in a row of cells,
& generating the respective number of outputs.
Feel free to like this post, send us a comment,
or subscribe to our channel.