1
00:00:00,830 --> 00:00:05,810
So now that we've learned a little bit about loops we can see an example of how we might use this in

2
00:00:05,810 --> 00:00:08,000
a real life Excel application.

3
00:00:08,000 --> 00:00:12,860
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

4
00:00:12,860 --> 00:00:17,300
these cells that says identify anything above this value as red.

5
00:00:17,360 --> 00:00:22,460
Make it bold and put a fail tag next to items that are smaller in value.

6
00:00:22,550 --> 00:00:25,240
And I've created a cell that we can enter that value into.

7
00:00:25,250 --> 00:00:27,780
So our minimum value in this case is five.

8
00:00:27,830 --> 00:00:34,510
And I can change that to be whenever I want it to be say six and if we come over to our first column

9
00:00:34,510 --> 00:00:35,610
here we can see.

10
00:00:35,610 --> 00:00:43,000
I've used excels intrinsic rand between function to randomly generate a number between 0 and 10 in all

11
00:00:43,000 --> 00:00:47,500
of these cells you can make it however many cells you want it to be.

12
00:00:47,510 --> 00:00:49,910
I just use 20 rows as an example.

13
00:00:49,910 --> 00:00:55,330
So I'll give you a second to pause and go ahead and set this up and that should have been enough time

14
00:00:55,330 --> 00:00:56,370
to pause.

15
00:00:56,380 --> 00:01:01,870
So now what we need to do is look at each of these cells and decide whether or not it is greater than

16
00:01:01,870 --> 00:01:03,960
whatever value we have specified over here.

17
00:01:03,970 --> 00:01:05,410
In this case six.

18
00:01:05,410 --> 00:01:09,840
So we'll look at the cell first and it's a three which is obviously less than six.

19
00:01:09,850 --> 00:01:13,430
And in this case we would want to put a failed tag by the number three.

20
00:01:14,050 --> 00:01:20,710
If the number of passes then we want to turn that number red and we want to make it bold so right off

21
00:01:20,710 --> 00:01:26,620
the bat we know we need an if statement to determine if this value meets our conditions of being greater

22
00:01:26,620 --> 00:01:29,080
than or equal to our specified number.

23
00:01:29,080 --> 00:01:32,790
And then we can either place the fail tag or change the font properties.

24
00:01:32,800 --> 00:01:37,840
So we also know this will need some sort of loop that can let us look at each of these cells and we

25
00:01:37,840 --> 00:01:41,440
need to know exactly how many of these cells there are that we need to be looking at.

26
00:01:41,590 --> 00:01:47,350
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

27
00:01:47,350 --> 00:01:52,540
you a second to pause and type it as well so we can talk through it and that should have been enough

28
00:01:52,540 --> 00:01:53,380
time to pause.

29
00:01:53,380 --> 00:01:59,350
So what we've done here is we've created a public sub and we've called this loop testing that we've

30
00:01:59,350 --> 00:02:02,520
gone ahead and we've created three different integers.

31
00:02:02,740 --> 00:02:07,990
In this case this are integer is going to stand for the number of rows that we need to look at in this

32
00:02:07,990 --> 00:02:14,490
case 20 test number is going to stand for the number that we're actually looking at.

33
00:02:14,520 --> 00:02:21,720
So this three this to this eight whatever number we're actually looking at and trying to assess Min

34
00:02:21,720 --> 00:02:27,880
number is going to represent this minimum value number here which we can change and this is what we're

35
00:02:27,880 --> 00:02:29,680
going to identify here.

36
00:02:29,750 --> 00:02:38,110
Number equal cells 5 5 value which means column 5 which is row and row 5 as well which is going to be

37
00:02:38,110 --> 00:02:40,390
our main value of six.

38
00:02:40,390 --> 00:02:44,710
Now it's finding the last row is a little bit more complicated.

39
00:02:44,770 --> 00:02:48,070
And so what we're going to do on this line is we're going to tell Excel.

40
00:02:48,280 --> 00:02:53,260
I want you to look at all of the cells you have in your workbook the cells function and I want you to

41
00:02:53,260 --> 00:02:59,890
count the number of rows that there are which is what this rows that count means.

42
00:02:59,890 --> 00:03:02,940
And specifically we want to look at column 1.

43
00:03:03,070 --> 00:03:09,010
Now what this will do is it will take Excel all the way down to the very last possible row in Excel

44
00:03:09,340 --> 00:03:11,920
which is way further than we need it to go.

45
00:03:11,950 --> 00:03:17,180
So the next thing we're going to tell it to do is come back up by using and excel up.

46
00:03:17,380 --> 00:03:21,280
And this is essentially the same thing as if you click somewhere in your Excel workbook.

47
00:03:21,280 --> 00:03:26,610
If you hold control and push your arrow key in this case I'm going to push the up arrow.

48
00:03:26,770 --> 00:03:29,920
It will jump you to your next available piece of data.

49
00:03:29,920 --> 00:03:31,300
And this works in any direction.

50
00:03:31,300 --> 00:03:36,910
So if I click out here and I hold control and click my left Eric key it'll bring me back over to the

51
00:03:36,910 --> 00:03:39,850
first cell that it finds data in.

52
00:03:40,300 --> 00:03:42,190
Lastly we have this row.

53
00:03:42,220 --> 00:03:48,310
So now we know we've told Excel to go to the very last row in the first column of this entire Excel

54
00:03:48,310 --> 00:03:50,890
worksheet which will be way at the bottom.

55
00:03:50,920 --> 00:03:56,870
We've brought it back up using and excel up which is like holding control and pushing the up arrow.

56
00:03:56,890 --> 00:04:01,340
So now we know we'll be looking at our very last piece of data that we have.

57
00:04:01,560 --> 00:04:05,080
And then specifically we want to know what that row number is.

58
00:04:05,080 --> 00:04:08,940
And we're going to store this in our integer are.

59
00:04:09,120 --> 00:04:15,030
So now that we know how many rows we actually need to look at we can go ahead and build our loop.

60
00:04:15,030 --> 00:04:22,140
So this here says 4 x equals 1 to R and I used x but you can use any variable that you want.

61
00:04:22,140 --> 00:04:26,820
And what this tells the system is that you're going to start with our X equals one and you're going

62
00:04:26,820 --> 00:04:29,960
to repeat everything that's within this for next loop.

63
00:04:29,970 --> 00:04:35,640
All of this information until you hit X is equal to our value.

64
00:04:35,640 --> 00:04:42,330
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

65
00:04:42,810 --> 00:04:44,270
and two is not 20 yet.

66
00:04:44,280 --> 00:04:49,410
So we're going to run this whole thing again and it will continue to increment up by one until we finally

67
00:04:49,410 --> 00:04:53,610
hit X equals 20 which will be the final time that this runs.

68
00:04:53,610 --> 00:04:58,350
So now we can look at everything we're doing within our loop we know now we are looping from cell 1

69
00:04:58,710 --> 00:04:59,910
to cell 20.

70
00:04:59,910 --> 00:05:05,550
So now we can actually determine OK the next thing we need to look at is what is our test number that

71
00:05:05,550 --> 00:05:07,260
we're trying to assess.

72
00:05:07,260 --> 00:05:15,290
So I created my variable test number up here and here I'm going to set it equal to my cells x 1 value.

73
00:05:15,300 --> 00:05:20,550
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

74
00:05:20,550 --> 00:05:28,140
at one comma one value which means row one column one a one which is three.

75
00:05:28,230 --> 00:05:33,330
The next time we run through this loop are X will have become two which means here I will have cells

76
00:05:33,350 --> 00:05:42,120
to come comma one value which is row two and again column 1 which is sell a 2 equal to 10 and this will

77
00:05:42,120 --> 00:05:47,160
repeat for each one of these cells until we hit our very last cell which will be the last time that

78
00:05:47,160 --> 00:05:48,750
this loop runs.

79
00:05:48,750 --> 00:05:54,420
So now we know we have successfully built our loop we've successfully identified the piece of information

80
00:05:54,420 --> 00:05:59,940
that we want to look at and now we get to build our if statement to determine what we actually do with

81
00:05:59,940 --> 00:06:07,570
that piece of data so if we jump back over here to our code we can see everything between this if and

82
00:06:07,600 --> 00:06:13,060
and if so all of this stuff in the middle is related to our if statement.

83
00:06:13,060 --> 00:06:18,260
So the first thing we want to do is go ahead and give this f statement some criteria.

84
00:06:18,260 --> 00:06:24,260
In this case we want to know if our test number which we just determined is greater than or equal to

85
00:06:24,290 --> 00:06:29,870
our minimum number which is the number that we specify in this cell here.

86
00:06:29,870 --> 00:06:35,170
If this criteria is met these are the actions that are going to be performed.

87
00:06:35,360 --> 00:06:41,150
And I know that these are the specific actions because this is what becomes before the else statement

88
00:06:41,150 --> 00:06:41,970
here.

89
00:06:41,990 --> 00:06:47,750
So everything between this F and this else happens only if our condition is met.

90
00:06:48,020 --> 00:06:52,240
Everything that happens after this else which is just this one line of code.

91
00:06:52,430 --> 00:06:54,350
And before the end it happens.

92
00:06:54,380 --> 00:06:56,700
If our condition is not met.

93
00:06:56,720 --> 00:07:03,110
So in this case if our test number is greater than or equal to our minimum number we are going to apply

94
00:07:03,260 --> 00:07:04,970
this set of actions.

95
00:07:04,970 --> 00:07:11,580
And if it's less than our minimum number we are going to apply this line of code here so first we'll

96
00:07:11,580 --> 00:07:14,640
look at this with and with that we've written.

97
00:07:14,640 --> 00:07:21,630
So this we're saying with ourselves x 1 so again it'll be this specific test no cell that we're looking

98
00:07:21,630 --> 00:07:22,220
at.

99
00:07:22,440 --> 00:07:27,180
We want to look at the fort property and with that from property we're going to look at two different

100
00:07:27,180 --> 00:07:28,060
things.

101
00:07:28,080 --> 00:07:29,850
First is whether or not it's bold.

102
00:07:30,270 --> 00:07:34,150
And second is whether or not that fourth property has a color applied to it.

103
00:07:34,180 --> 00:07:37,140
And we're going to change the bold property be true.

104
00:07:37,200 --> 00:07:43,110
We're gonna change the color to be right which we can use a color code if you want to you can also just

105
00:07:43,110 --> 00:07:47,370
use VB red which will turn the font to normal red color.

106
00:07:47,370 --> 00:07:50,400
And this is all that we want to mess with for this property.

107
00:07:50,400 --> 00:07:57,210
So this is where we'll end with statement and again this color is going to apply directly to this sells

108
00:07:57,330 --> 00:08:04,530
X comma one dot font it would be the same thing as typing dot color after this or dot bold after this.

109
00:08:04,860 --> 00:08:10,450
So we're just saving ourselves some time by not having to repeat our object hierarchy.

110
00:08:10,500 --> 00:08:16,230
Now if this condition is not met if we are less than on note on a number that we specified we were going

111
00:08:16,230 --> 00:08:23,550
to apply this line of code and this says sells X to that value so you'll notice we've moved over one

112
00:08:23,550 --> 00:08:25,500
column from our test number.

113
00:08:25,530 --> 00:08:28,110
So now we're looking in column two here.

114
00:08:28,110 --> 00:08:32,850
We're going to change the value of that cell to be failed which will be this failed tag that we talked

115
00:08:32,850 --> 00:08:34,350
about here.

116
00:08:34,360 --> 00:08:39,330
Now I'll go ahead and step through this line by line so we can see exactly how it all behaves.

117
00:08:39,330 --> 00:08:41,820
So I'll press f 8 to enter my code.

118
00:08:41,850 --> 00:08:48,750
You can also do this from the debug menu so we'll start by identifying the number of rows that are in

119
00:08:49,440 --> 00:08:53,370
our file and we can see it's picked up the number 20.

120
00:08:53,460 --> 00:08:57,390
And this is one of the most helpful things you can do in Excel VBA.

121
00:08:57,480 --> 00:09:02,640
It lets your data ranges be dynamic so it doesn't matter if I come back in later and I add 10 extra

122
00:09:02,640 --> 00:09:04,950
rows or I take off five rows.

123
00:09:04,950 --> 00:09:09,570
I don't need to come in and manually addressed my code by code will automatically pick up that change

124
00:09:09,570 --> 00:09:10,260
for me.

125
00:09:11,440 --> 00:09:13,450
This line here should pull the minimum value.

126
00:09:13,450 --> 00:09:18,540
Number Six and if I have over this we can see it's pulled six.

127
00:09:18,560 --> 00:09:25,620
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

128
00:09:25,620 --> 00:09:33,060
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.

129
00:09:33,080 --> 00:09:35,790
So we know we're pulling from cell a 1.

130
00:09:35,870 --> 00:09:37,730
Now we're going to look at our condition here.

131
00:09:37,730 --> 00:09:43,100
So we want to know if our test number which is three is greater than or equal to our minimum number

132
00:09:43,250 --> 00:09:44,510
which is six.

133
00:09:44,510 --> 00:09:50,720
In this case it should fail this criteria and we should see this jump down to the else statement a press

134
00:09:50,780 --> 00:09:54,780
F eight and we can see I've jumped down to my else statement.

135
00:09:54,810 --> 00:09:57,300
So we've entirely skipped all of these steps here.

136
00:09:57,300 --> 00:10:01,810
This with and with that we've built because this criteria was not met.

137
00:10:01,830 --> 00:10:08,580
So if I push f 8 again we should see it enter this line of code because their criteria was failed I'll

138
00:10:08,610 --> 00:10:13,820
push f eight one more time and we can see my failed tag has showed up here.

139
00:10:13,860 --> 00:10:16,330
Now you'll notice the numbers have actually changed.

140
00:10:16,350 --> 00:10:21,260
This is because the random between will regenerate as you're working.

141
00:10:21,270 --> 00:10:26,610
So usually you'd want to copy and paste these values and get rid of your ran between statement here

142
00:10:27,030 --> 00:10:32,400
and if you actually just run this as a normal piece of code this will automatically remain the same

143
00:10:32,400 --> 00:10:33,520
while you're running your code.

144
00:10:33,660 --> 00:10:38,400
But just as an example we'll go ahead and step through one more loop so we can see how this will work.

145
00:10:38,550 --> 00:10:48,390
We'll go to our next x value we jumped back up reenter our loop but this time our X equals to push effort.

146
00:10:48,400 --> 00:10:52,630
Now I can look at my test number and I can see we've pulled the number one which makes sense because

147
00:10:52,630 --> 00:10:54,660
we should be looking at cell a two.

148
00:10:55,090 --> 00:10:59,860
And again if we step through this loop we should see it fails and we'll keep moving on.

149
00:11:01,040 --> 00:11:07,010
So I get to go ahead and stop this code by pushing the reset button and I'm going to clear out my column

150
00:11:07,010 --> 00:11:12,340
B we can see again my random between has regenerated here.

151
00:11:12,650 --> 00:11:14,090
I will copy this column

152
00:11:17,290 --> 00:11:23,470
and I will paste this column over itself but I'm going to pay special I'm going to paste only the values

153
00:11:23,500 --> 00:11:28,780
and what this will do is it will clear out that ran between statement that we use to get this list so

154
00:11:28,780 --> 00:11:34,090
I'll paste my values and I want to click in these cells we can see there's no longer formula up here

155
00:11:34,090 --> 00:11:41,080
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

156
00:11:41,080 --> 00:11:41,830
through the whole thing.

157
00:11:43,440 --> 00:11:45,710
And we can see this has successfully worked.

158
00:11:45,720 --> 00:11:50,890
We have our values that are greater than or equal to the number six have been highlighted red.

159
00:11:50,940 --> 00:11:57,670
They've been made bold and the items that failed that criteria got our failed tag so we talked a little

160
00:11:57,670 --> 00:12:00,130
bit about these different types of loops.

161
00:12:00,130 --> 00:12:05,920
There are this for next loop there's also for each loop and I do until loop and we talk through each

162
00:12:05,920 --> 00:12:12,370
of those we talked through if statements and with and with and this is a good summary example of how

163
00:12:12,370 --> 00:12:18,670
you might apply all of those and you can use these in as complex or as simple situations as you want

164
00:12:18,670 --> 00:12:18,940
to.

165
00:12:18,940 --> 00:12:24,340
So they are very powerful tools to understand and to know how to use in Microsoft VBA.