Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
Now I'm going to talk about loops in Excel in Macros, and they are extremely important thing which actually help you do
00:10
those things that ah
00:12
cannot be done in excel itself without macros.
00:16
So we have three types of groups in excel, and each of these types have subtypes.
00:24
So we have four next loop we have do while and do until
00:29
you'll see later to do while and do until our
00:32
essentially
00:34
two
00:35
off the same. They just have a different kind of condition.
00:40
Ah, that will end the loop.
00:43
So, um four next loop has two variations recharge
00:48
substantially different. So we have four next,
00:52
and we have for each next. And that was just not the theorize anymore.
00:58
Let's just go to the
01:02
examples.
01:04
And, uh
01:07
So, uh,
01:08
first of all I have we have that function in Excel. But I have made
01:15
the macro that will calculate
01:18
factorial all the number
01:22
and with me first see that
01:26
it's very simple.
01:29
So
01:30
it's
01:32
called F C. T.
01:34
And we defined calculated. This is double
01:40
and defined Eye is an integer,
01:42
and we at the beginning say, Calculated is one because we're going to multiply it with
01:51
uh,
01:52
value And the value is in
01:57
the maximum valued. I mean, the number that we're looking to calculate factorial is in cell a one.
02:04
So it says four I equals one to value off. A one
02:10
means that it will
02:13
change I in every pass through the Luke
02:16
from adding one.
02:20
So
02:21
we then say calculated equals calculated times I and then we say next I
02:28
So at this point, it looks if I is
02:31
equal the number 81
02:35
and if it doesn't, it goes back to four.
02:38
If it is,
02:39
it will end the look.
02:43
And now we go to
02:46
just put in selling to the value off calculated pictorial.
02:51
Well, we will say range off, sell a two, and its value equals
02:57
calculated and real and the subroutine.
03:00
And let's just
03:02
close this
03:06
and just the
03:08
ship.
03:10
Yeah, and run it
03:20
then. So let's hear. Put number five
03:23
and, uh,
03:24
go to Mac Rose and ran factorial, and we'll get 520 which is exact number.
03:31
So why have we decided to put Factorial to be double that? We put it here like we put here like
03:39
10,000
03:44
and
03:46
then we run macro pictorial
03:49
and we'll get overflow. It's even bigger than double.
03:53
So it's put smaller number, which is, for example, 75.
03:57
All right here, 75
04:00
and then run this macro
04:06
and we get this number. So it's a very big one.
04:11
Yeah,
04:12
that is the
04:14
common knowledge about factorial function. It grows very high as the numbers get higher. So this is how this one works,
04:23
and let's just do the other.
04:28
So let's put something in these cells
04:32
and
04:33
let's first execute the macro,
04:38
which is which is called Sells Red,
04:42
and
04:43
I would select cells,
04:45
and it will paint the cells red if they're hired in certain number and blue. If there
04:51
lowered, insist the number.
04:54
So let's
04:56
look at this.
05:06
My crew.
05:08
So they're in mentioning, See cell as range
05:13
and selected sells his Rangers will
05:16
and then we say, set selected sells equal application selection
05:21
so selected cells is arrange that consists off dull cells that we have selected in the worksheet,
05:28
and this is the function or command within visual basic for applications that will return into this range. Because this is range,
05:39
it will return this
05:42
selection as arranged so it could be one cell it can be to sells. It could be 5000 cells in two dimensions. It will just returned itself. So then
05:53
we say, for each C cell, which is self current cell in selected sells dot cells. So we're looking at the cell's off this range.
06:04
And if C cell value is greater than 10
06:10
then we'll paint the cell red
06:13
else.
06:15
Well,
06:15
c cell interior, color function, and we'll paint it visual. Basic blue.
06:21
We could have used RGB function, which would give us three parameters, which would paint the cell in pretty much any core we want.
06:32
But, uh
06:34
uh, this is just the thing that was done, for example. So we had
06:42
red. If this is hired, and then blow if this if it's equal or lower than 10 and then and the end, if we have finished the painting yours off this self and then we go to next Cecil
06:55
and it will continue this loop until
06:59
it goes through, all off these cells in selected sells range, and then it will
07:06
exit Luke and it will go toe ends up.
07:11
So ah, this way. We have
07:15
done successful for each
07:18
and next
07:20
group

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor