Alright. I am going to give you kind of an introduction to arrays in this screencast,

particularly arrays as we define them in Excel.

So this is a selection.

It is also what we refer to as a array.

It has got two or more rows and two or more columns.

A vector is kind of a special case of an array.

A vector is where either the number of rows or the number of columns is equal to 1.

In this course, I am going to refer to vectors as a n by one.

So, here, we have n rows,

and we have six rows but only one column,

so that is going to be a vector.

I could also have a one by four and this would also be a vector, an array.

Then, as I am going to define them in this course,

is where you have two or more rows and columns.

So, this is a selection.

This is also an array.

We can also refer to this as range A1 to C3.

Oftentimes, you will want to work with this array,

and when you're working with arrays I would encourage you to use nested for loops,

and let me show you what I mean by this.

So I have got just a generic three by four array,

and we have got first, second, third, fourth.

We have four items in each row,

and what I am doing here is naming them row Y.

So we fill up the first row,

then we move to the second row,

and then we move to the third row.

I like to define this index i that refers to the row number.

So we are going to kind of have this outer for loop,

where we are going to iterate through the row number and

i is going to go from 1 to 3 in this case.

If we have got more rows,

then i would go from 1 to the number of rows.

Inside this for loop,

we have an inner for loop and,

in this case then,

when we fix i, for example,

when i equals 1, we are looking at the first row.

Then we are going to set j, a second index of iteration going from 1 to 4.

So we would fix i, the outer loop,

and then we would iterate through j equals 1, j equals 2,

j equals 3 and j equals 4.

Then i would get bumped up to 2.

Now, we'd would be on the second row.

j would be reset to 1. j would go 1 through 4,

then we get bumped up to the next i,

which would be 3. j would go from 1 to 4.

The order of the first, second,

third and so on would represent the order that

we iterate through using these nested for loops here.

So let me show you an example.

This is just going to be a pretty simple example,

where we want to add 5 to each element in a selection.

So this is our selection.

We have to make sure that we select it.

So that is our selection, my AddFive.

The first thing I am going to do is dim i and j. i and j

are going to be our row and column indices respectively.

Next, we need to count the number of rows and the number of columns of our selection,

but we first need to dim those variables.

After we have counted the number of rows of our selection and the number of columns,

then we can enter into our outer for loop.

So our outer for loop is going to be iterating through the rows.

Inside this outer for loop,

we are going to have an inner for loop,

in which we iterate through the columns.

So I've set up my inner for loop.

And, now, inside the inner for loop,

we just need to put in what we are going to be doing to each of these cells.

So the new Selection.Cells (i,

j) is going to be equal to the old 1, plus 5.

So, here, I have put Selection.Cells (i,

j) is going to be the old Selection.Cells (i, j) plus 5.

So we are going to iterate through all for i equals 1,

2, 3, for j equals 1 to 3.

So let us go ahead and do this.

I am going to press F8.

We obtain the number of rows and the number of columns,

3 and 3, respectively.

Now, we enter into i is equal to 1, J is equal to 1.

So we are going to set selection (1,

1) equals selection (1, 1) plus 5.

So we are going to add 5 to that 1,

and that will be the new Selection.Cells (1,

1). It adds 5.

Now, we bump up i is still 1. j is now going to be 2.

So we just go through the inner loop for each iteration of the outer loop.

Now j is equal to 2.

We have replaced (1,

2) with 7, and we keep going.

Now, i gets bumped up by 1.

Now, i is equal to 2, so j gets reset to 1.

Now, we are looking at the second row here, position (2, 1).

We had 5 and we go to the (2, 2).

We add 10. We go to the (2, 3).

We add, we get 11. i gets bumped up to 3. j gets reset to 1,

so now we are looking at (3, 1).

7 plus 5 is 12 and we go to the second, the (3,

2) position and finally the (3,

3) position, and we are done.

Let us do a slightly different example.

Now, we wish to add 5 to the elements of

a selection but place the result in a different range.

We do not want to replace this selection.

We want to sort of offset and put the result down below in the same columns,

but the rows are going to be different.

We are going to be skipping a row.

Now, we want to make this such that it is not just for

a three by three selection but is for any selection.

And you notice here,

just as an example,

if I wanted to put the new (1,

1) position down below here,

it is going to be in the same column,

but it is one, two, three,

four rows down, so we are going to offset by one, two, three, four.

That four, we can get from

any selection in general by just taking the number of rows and adding 1.

So, if I had a selection that had five rows,

then I would offset six rows down.

So I start with my selection.

You notice that A1 is the active cell.

We are going to use the ActiveCell.offset.

So we are going to still do the same thing,

where we dim i, j,

nr, nc, all as integers.

So we count the number of rows,

count the number of columns.

And, now, what I am going to do is instead of Selection.Cells (i, j),

what I want to do is I want to offset from the active cell,

so I replace that Selection.Cells (i,

j) with ActiveCell.Offset (nr+1).

That is going to be the number of rows that we offset.

If I am going to do it this way,

I have to make this row number in terms of i,

which is the row index.

So when i equals 1, I am working on the first row,

and I want to offset by nr plus one but I somehow have to have

a formula in terms of i there and because i is equal to 1,

I can just do, plus i minus 1.

Those ones cancel and it just becomes ActiveCell.Offset (nr + i,

0) = Selection.Cells (i, j) plus 5.

So we can check this because if i equals 1,

then I want to offset by 1, 2, 3.

So that is nr 1.

If i equals 3, for example,

that would be a 3 here,

then I need to offset 1, 2,

3 which is the number of rows and then another 1,

2, 3, which we put the result down in this cell.

We also, because the active cell is never going to change,

we need to change this because if j equals 2,

we want to be offsetting by one column.

If j equals 1,

we do not want to be offsetting by any.

So, in general, we are going to take j minus 1,

and that is how many columns we are going to offset.

So I think we are ready to go. I am going to start with my selection.

We can go ahead and run through this,

count the number of rows, number of columns.

We enter into our inner for loop.

So we offset by four rows because nr is 3,

i is equal to 1.

We put the 6 there,

and now we go to j equals 2.

We are in the second column.

Now, j equals 2, we are going to subtract 1 and we are going to offset by one column,

and we place the result in the adjacent cell.

Next, j is 3,

so we added over there.

Now, we bumped to the next i. j gets reset to 1,

and we ActiveCell.Offset in this case,

3 plus 2, which is 5.

Keep in mind that A1 is still the active cell.

It is going to be the active cell the entire time and we keep going.

And that is how you can then place

the result of the adding 5 to each element of a selection,

but place it in another area of the spreadsheet.

So, hopefully, you learned just kind of the basics of

iterating through arrays in Excel with VBA.