So now that we've learned a little bit about loops we can see an example of how we might use this in
a real life Excel application.
So I've gone ahead and I've created this sheet here and what I've done is I've put a little box by emerging
these cells that says identify anything above this value as red.
Make it bold and put a fail tag next to items that are smaller in value.
And I've created a cell that we can enter that value into.
So our minimum value in this case is five.
And I can change that to be whenever I want it to be say six and if we come over to our first column
here we can see.
I've used excels intrinsic rand between function to randomly generate a number between 0 and 10 in all
of these cells you can make it however many cells you want it to be.
I just use 20 rows as an example.
So I'll give you a second to pause and go ahead and set this up and that should have been enough time
to pause.
So now what we need to do is look at each of these cells and decide whether or not it is greater than
whatever value we have specified over here.
In this case six.
So we'll look at the cell first and it's a three which is obviously less than six.
And in this case we would want to put a failed tag by the number three.
If the number of passes then we want to turn that number red and we want to make it bold so right off
the bat we know we need an if statement to determine if this value meets our conditions of being greater
than or equal to our specified number.
And then we can either place the fail tag or change the font properties.
So we also know this will need some sort of loop that can let us look at each of these cells and we
need to know exactly how many of these cells there are that we need to be looking at.
So let's go ahead and jump over to our VBA window and I've gone ahead and typed this out so I'll give
you a second to pause and type it as well so we can talk through it and that should have been enough
time to pause.
So what we've done here is we've created a public sub and we've called this loop testing that we've
gone ahead and we've created three different integers.
In this case this are integer is going to stand for the number of rows that we need to look at in this
case 20 test number is going to stand for the number that we're actually looking at.
So this three this to this eight whatever number we're actually looking at and trying to assess Min
number is going to represent this minimum value number here which we can change and this is what we're
going to identify here.
Number equal cells 5 5 value which means column 5 which is row and row 5 as well which is going to be
our main value of six.
Now it's finding the last row is a little bit more complicated.
And so what we're going to do on this line is we're going to tell Excel.
I want you to look at all of the cells you have in your workbook the cells function and I want you to
count the number of rows that there are which is what this rows that count means.
And specifically we want to look at column 1.
Now what this will do is it will take Excel all the way down to the very last possible row in Excel
which is way further than we need it to go.
So the next thing we're going to tell it to do is come back up by using and excel up.
And this is essentially the same thing as if you click somewhere in your Excel workbook.
If you hold control and push your arrow key in this case I'm going to push the up arrow.
It will jump you to your next available piece of data.
And this works in any direction.
So if I click out here and I hold control and click my left Eric key it'll bring me back over to the
first cell that it finds data in.
Lastly we have this row.
So now we know we've told Excel to go to the very last row in the first column of this entire Excel
worksheet which will be way at the bottom.
We've brought it back up using and excel up which is like holding control and pushing the up arrow.
So now we know we'll be looking at our very last piece of data that we have.
And then specifically we want to know what that row number is.
And we're going to store this in our integer are.
So now that we know how many rows we actually need to look at we can go ahead and build our loop.
So this here says 4 x equals 1 to R and I used x but you can use any variable that you want.
And what this tells the system is that you're going to start with our X equals one and you're going
to repeat everything that's within this for next loop.
All of this information until you hit X is equal to our value.
So the first time we run this X will be equal to one and then we'll hit our next X X will become two
and two is not 20 yet.
So we're going to run this whole thing again and it will continue to increment up by one until we finally
hit X equals 20 which will be the final time that this runs.
So now we can look at everything we're doing within our loop we know now we are looping from cell 1
to cell 20.
So now we can actually determine OK the next thing we need to look at is what is our test number that
we're trying to assess.
So I created my variable test number up here and here I'm going to set it equal to my cells x 1 value.
Now what this means is that when my X is equal to 1 which is the very first time we loop we'll be looking
at one comma one value which means row one column one a one which is three.
The next time we run through this loop are X will have become two which means here I will have cells
to come comma one value which is row two and again column 1 which is sell a 2 equal to 10 and this will
repeat for each one of these cells until we hit our very last cell which will be the last time that
this loop runs.
So now we know we have successfully built our loop we've successfully identified the piece of information
that we want to look at and now we get to build our if statement to determine what we actually do with
that piece of data so if we jump back over here to our code we can see everything between this if and
and if so all of this stuff in the middle is related to our if statement.
So the first thing we want to do is go ahead and give this f statement some criteria.
In this case we want to know if our test number which we just determined is greater than or equal to
our minimum number which is the number that we specify in this cell here.
If this criteria is met these are the actions that are going to be performed.
And I know that these are the specific actions because this is what becomes before the else statement
here.
So everything between this F and this else happens only if our condition is met.
Everything that happens after this else which is just this one line of code.
And before the end it happens.
If our condition is not met.
So in this case if our test number is greater than or equal to our minimum number we are going to apply
this set of actions.
And if it's less than our minimum number we are going to apply this line of code here so first we'll
look at this with and with that we've written.
So this we're saying with ourselves x 1 so again it'll be this specific test no cell that we're looking
at.
We want to look at the fort property and with that from property we're going to look at two different
things.
First is whether or not it's bold.
And second is whether or not that fourth property has a color applied to it.
And we're going to change the bold property be true.
We're gonna change the color to be right which we can use a color code if you want to you can also just
use VB red which will turn the font to normal red color.
And this is all that we want to mess with for this property.
So this is where we'll end with statement and again this color is going to apply directly to this sells
X comma one dot font it would be the same thing as typing dot color after this or dot bold after this.
So we're just saving ourselves some time by not having to repeat our object hierarchy.
Now if this condition is not met if we are less than on note on a number that we specified we were going
to apply this line of code and this says sells X to that value so you'll notice we've moved over one
column from our test number.
So now we're looking in column two here.
We're going to change the value of that cell to be failed which will be this failed tag that we talked
about here.
Now I'll go ahead and step through this line by line so we can see exactly how it all behaves.
So I'll press f 8 to enter my code.
You can also do this from the debug menu so we'll start by identifying the number of rows that are in
our file and we can see it's picked up the number 20.
And this is one of the most helpful things you can do in Excel VBA.
It lets your data ranges be dynamic so it doesn't matter if I come back in later and I add 10 extra
rows or I take off five rows.
I don't need to come in and manually addressed my code by code will automatically pick up that change
for me.
This line here should pull the minimum value.
Number Six and if I have over this we can see it's pulled six.
Now we're going to enter our loop so we can start with X equals 1 so if I hover over this X here we
can see right now X is equal to 1 which means once I press f 8 this should be equal to 3 which it is.
So we know we're pulling from cell a 1.
Now we're going to look at our condition here.
So we want to know if our test number which is three is greater than or equal to our minimum number
which is six.
In this case it should fail this criteria and we should see this jump down to the else statement a press
F eight and we can see I've jumped down to my else statement.
So we've entirely skipped all of these steps here.
This with and with that we've built because this criteria was not met.
So if I push f 8 again we should see it enter this line of code because their criteria was failed I'll
push f eight one more time and we can see my failed tag has showed up here.
Now you'll notice the numbers have actually changed.
This is because the random between will regenerate as you're working.
So usually you'd want to copy and paste these values and get rid of your ran between statement here
and if you actually just run this as a normal piece of code this will automatically remain the same
while you're running your code.
But just as an example we'll go ahead and step through one more loop so we can see how this will work.
We'll go to our next x value we jumped back up reenter our loop but this time our X equals to push effort.
Now I can look at my test number and I can see we've pulled the number one which makes sense because
we should be looking at cell a two.
And again if we step through this loop we should see it fails and we'll keep moving on.
So I get to go ahead and stop this code by pushing the reset button and I'm going to clear out my column
B we can see again my random between has regenerated here.
I will copy this column
and I will paste this column over itself but I'm going to pay special I'm going to paste only the values
and what this will do is it will clear out that ran between statement that we use to get this list so
I'll paste my values and I want to click in these cells we can see there's no longer formula up here
it is just the value of the cell I'll come back into my code and this time I'll just click play to run
through the whole thing.
And we can see this has successfully worked.
We have our values that are greater than or equal to the number six have been highlighted red.
They've been made bold and the items that failed that criteria got our failed tag so we talked a little
bit about these different types of loops.
There are this for next loop there's also for each loop and I do until loop and we talk through each
of those we talked through if statements and with and with and this is a good summary example of how
you might apply all of those and you can use these in as complex or as simple situations as you want
to.
So they are very powerful tools to understand and to know how to use in Microsoft VBA.