Video Transcription

00:00

So you have seen that

00:02

in four. Next

00:04

Ah

00:05

ah, loop. We have

00:09

counter as essential part off the

00:12

loop structure

00:14

in do while we don't have to have it.

00:17

So

00:19

we have two variations which is due while and then loop at the end. Or do something loop while

00:25

they're similar in, but essentially different. So we'll have

00:31

both of them

00:33

explained in the examples. So let's go first to this one, which is just the stuff straight do,

00:43

uh, while and then loop.

00:46

And in this particular case,

00:50

we want to calculate,

00:52

um,

00:54

the largest sum off

00:57

integers

00:59

that will be smaller than the given number.

01:02

So we type number in this cell

01:04

and then we want to calculate so one plus two plus three plus four plus something

01:11

and that's total sum should be smaller than this number.

01:15

And let's just do the

01:19

example first. So let's run. I call this maxim

01:23

and I get 1378. I didn't display what is the last number, but let's go to the structure of this macro

01:34

and explain how it's

01:38

done.

01:38

So it's closed this one first.

01:42

So we have this sub and then we dimension dies an integer

01:49

and em Some also is an integer so we won't go

01:52

do high

01:53

And at the beginning we set em some and I to zero

01:57

And then we say Do while

02:00

m Some

02:01

is smaller than active cell value. So this is how we recall the active cell because this

02:08

ah macro has designed that we select the cell

02:13

and then it will put in the cell next to it.

02:16

The result.

02:17

So then we

02:20

menu Aly create the counter. So we say I equals eye plus one

02:25

and then we say m some equals m sum plus one and then we do loop

02:31

and it will go back

02:35

and check if M sum is still smaller than active cell.

02:38

And if it is,

02:40

it will

02:43

add one toe I and do the sub sub summation again and then

02:50

rotate until we

02:53

make em some bigger than active cell value.

02:57

Then, at that moment, we have already added one number too many

03:02

to the total sum. But we have exited the loop and then we're just putting in efs active cell.

03:09

And then we were using the perimeter offset. So with the active cell officer 0.1 means zeroes and one column

03:19

higher location than the active cell

03:23

and then value.

03:24

And then we just

03:28

make it Toby M. Some miners, I so

03:31

one I added too many. We have subtracted and we have ended this up. So you see here,

03:38

this is

03:39

moving around in the loop until we reached this condition.

03:45

In theory, we didn't have to have any kind of,

03:49

ah counter here.

03:52

We could have had ah, for example of

03:55

displayed box in which you were would be entering something yes or no? Yes or no. And regarding off the result, it would

04:08

loup loup, loup, loup, loup until

04:11

and

04:12

so this is how this one works.

04:15

Let's close this for a moment

04:16

and let's go here. So

04:19

the idea of this one is to select the range of cells

04:24

and then put to the cells next to them square root of this number here.

04:30

So

04:31

it's this function and let's edit it first.

04:35

So we're

04:41

saying

04:42

that counter is an integer

04:45

and we'd say, Do

04:46

e I was I plus one and then we say range off e,

04:55

which is

04:57

call him e.

04:58

And then

05:00

I as in

05:00

counter which represent the row

05:06

and then the value of it is equal off square root

05:12

off the cell, which is in column D ended in the same row

05:17

and what we're doing, then we're looping

05:23

while range

05:26

value off the cell in range Incom D

05:30

is different from nothing.

05:32

When we reach the first cell

05:36

that has no values in it,

05:39

we say, Okay, we have calculated them all.

05:42

And then we're just the last cell. We're putting nothing in it because we have already calculated it could be square root off nothing which is zero. And then it would be a zero in that cell. So let's just

05:58

we don't have to select the range, which is go here

06:01

and execute square with Dole

06:03

and it will calculate square roots of these numbers

06:08

and just these numbers. So when this

06:12

macro reaches empty cell, it will stop.

06:17

And, uh,

06:20

let's immediately do the do until

06:25

with the absolutely same examples. So I have

06:28

ah, first maxim, too.

06:30

It gives the same result like before,

06:33

and we who, if we would edit this function,

06:38

we see that we're doing pretty much the same thing.

06:41

Accept, because it's now until

06:45

the condition is different.

06:47

There we had that we were doing this

06:50

while I am some

06:53

active cell value is smaller easel larger

06:59

em. Some is smaller than active cell value and here were doing until we cross the threshold. Effective cell value

07:09

pretty much the same thing.

07:11

So we're just going in the loop,

07:15

adding And then again when we reach this condition, we have already added one number too many. So at the end,

07:25

the value off active cell plus one

07:28

location in the in the same row will be the calculated some minus the last counter.

07:36

And that's the one.

07:41

And the second

07:43

is the same here. So we're going to calculate

07:46

square a toll

07:48

two. We get absolutely the same results.

07:51

And

07:55

if we added this macro,

07:57

we're doing the same thing. Except we're doing global till.

08:03

So we're until the range of the is equal. Nothing. So there we were looking if it's different than nothing. And here we are now looking for empty cell, and when we find them to sell, we say, Okay, stop.

08:16

And then we just put in the last cell because we have already done the calculation and did the square root.

08:24

We just put

08:24

the empty in. The

08:28

result.

08:28

Calm in the cold e

08:31

and we're finished. So these are the loops in excel,

08:37

and you have seen how to efficiently use them

08:41

in the various calculation. So to do this

08:46

in Excel without Mac Rose,

08:48

to do it on a range that is absolutely variable would be

08:54

quite a difficult thing. It could be possibly done with some array functions and some functions, but it's a

09:01

it would be a problem, if not impossible. So this is very easy way you see how short this macro is. It's very easy way to do something like that.

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