8 hours 33 minutes
So you have seen that
in four. Next
ah, loop. We have
counter as essential part off the
in do while we don't have to have it.
we have two variations which is due while and then loop at the end. Or do something loop while
they're similar in, but essentially different. So we'll have
both of them
explained in the examples. So let's go first to this one, which is just the stuff straight do,
uh, while and then loop.
And in this particular case,
we want to calculate,
the largest sum off
that will be smaller than the given number.
So we type number in this cell
and then we want to calculate so one plus two plus three plus four plus something
and that's total sum should be smaller than this number.
And let's just do the
example first. So let's run. I call this maxim
and I get 1378. I didn't display what is the last number, but let's go to the structure of this macro
and explain how it's
So it's closed this one first.
So we have this sub and then we dimension dies an integer
and em Some also is an integer so we won't go
And at the beginning we set em some and I to zero
And then we say Do while
is smaller than active cell value. So this is how we recall the active cell because this
ah macro has designed that we select the cell
and then it will put in the cell next to it.
So then we
menu Aly create the counter. So we say I equals eye plus one
and then we say m some equals m sum plus one and then we do loop
and it will go back
and check if M sum is still smaller than active cell.
And if it is,
add one toe I and do the sub sub summation again and then
rotate until we
make em some bigger than active cell value.
Then, at that moment, we have already added one number too many
to the total sum. But we have exited the loop and then we're just putting in efs active cell.
And then we were using the perimeter offset. So with the active cell officer 0.1 means zeroes and one column
higher location than the active cell
and then value.
And then we just
make it Toby M. Some miners, I so
one I added too many. We have subtracted and we have ended this up. So you see here,
moving around in the loop until we reached this condition.
In theory, we didn't have to have any kind of,
ah counter here.
We could have had ah, for example of
displayed box in which you were would be entering something yes or no? Yes or no. And regarding off the result, it would
loup loup, loup, loup, loup until
so this is how this one works.
Let's close this for a moment
and let's go here. So
the idea of this one is to select the range of cells
and then put to the cells next to them square root of this number here.
it's this function and let's edit it first.
that counter is an integer
and we'd say, Do
e I was I plus one and then we say range off e,
call him e.
I as in
counter which represent the row
and then the value of it is equal off square root
off the cell, which is in column D ended in the same row
and what we're doing, then we're looping
value off the cell in range Incom D
is different from nothing.
When we reach the first cell
that has no values in it,
we say, Okay, we have calculated them all.
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
we don't have to select the range, which is go here
and execute square with Dole
and it will calculate square roots of these numbers
and just these numbers. So when this
macro reaches empty cell, it will stop.
let's immediately do the do until
with the absolutely same examples. So I have
ah, first maxim, too.
It gives the same result like before,
and we who, if we would edit this function,
we see that we're doing pretty much the same thing.
Accept, because it's now until
the condition is different.
There we had that we were doing this
while I am some
active cell value is smaller easel larger
em. Some is smaller than active cell value and here were doing until we cross the threshold. Effective cell value
pretty much the same thing.
So we're just going in the loop,
adding And then again when we reach this condition, we have already added one number too many. So at the end,
the value off active cell plus one
location in the in the same row will be the calculated some minus the last counter.
And that's the one.
And the second
is the same here. So we're going to calculate
square a toll
two. We get absolutely the same results.
if we added this macro,
we're doing the same thing. Except we're doing global till.
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.
And then we just put in the last cell because we have already done the calculation and did the square root.
We just put
the empty in. The
Calm in the cold e
and we're finished. So these are the loops in excel,
and you have seen how to efficiently use them
in the various calculation. So to do this
in Excel without Mac Rose,
to do it on a range that is absolutely variable would be
quite a difficult thing. It could be possibly done with some array functions and some functions, but it's a
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.
Data Analyst Excel
The Data Analyst Excel Test is a premium Cybrary assessment created by iMocha and intended ...
Monitor a Web App
In this IT Pro challenge, participants get hands-on experience activating server logging and using Application ...
Learn On Demand