Hello, this is a follow up to an earlier video on
writing a simple loop in Excel VBA.
However in this video, we will compose a
basic loop macro of rows in a column in
Excel using Visual Basic for Applications.
Most Excel users are aware we can drag down
a couple of cells like this, in order to generate a
pattern of cell values down rows in a column.
But instead we will draft
a simple macro to perform the same output.
Go to Visual Basic
for Applications, and insert a Module.
The primary aim from this video is to
demonstrate how Excel users can write
a simple looping macro
based on a single parameter.
More specifically,
we will write a looping macro to loop through
rows in a column in a spreadsheet.
We will call this macro, loopRowsColumn
Next we should disable these Excel properties,
which denote the calculation,
event and screen properties of Excel.
Our macro will function more efficiently
by disabling these properties,
whilst we execute on our looping code.
Then we want to declare our object
variables for this macro.
The i object will be declared as a long variable,
which can store long numeric values.
Whilst both Rng and startCell will be declared
as range object variables, because these
objects need to denote
cells or multiple cells in VBA.
The startCell range object will be declared as
the starting or active cell for this macro.
On the other hand, rng will
be the selected range of cells.
Next is the major learning aspect of this video;
the basic looping code to cycle down rows
in a column based on a simple criteria.
The For ... Next loop is one technique
to loop through a range of cells.
In this example, the loop will execute
based on the i object variables of 1 to 10.
Given we need 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 = 1.
when i = 1. Given we don't want to shift the cursor down or up any rows nor columns for the first loop.
Given we don't want to shift the cursor down
or up any rows nor columns for the first loop.
Thus the first loop of i, 1, will
return the output value of 1.
The i object variable can now move to 2, in
order for this loop to run again - the cursor will
move one row down 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 return the cursor
back to the initial cell or startCell object variable.
The looping is complete, we can
now re-enable those three Excel properties that
were disabled at the start of our macro.
Our macro has finished running.
Before we test the macro in our spreadsheet,
why not review the macro one last time.
Generally speaking, it is advisable to add the
Option Explicit syntax at the top of the module.
The Option Explicit will ensure we declare all of
the variables in a
specific macro such as this one.
It will reduce the risk of run-time errors
whilst the macro is running.
Let's run the macro.
As we can see, the macro is correctly looping
down the rows in a column of cells, and
returning the individual number outputs.
If you want to like this post, send us a comment
or subscribe to our channel.