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

Similar Content