8 hours 33 minutes
Now I'm going to talk about loops in Excel in Macros, and they are extremely important thing which actually help you do
those things that ah
cannot be done in excel itself without macros.
So we have three types of groups in excel, and each of these types have subtypes.
So we have four next loop we have do while and do until
you'll see later to do while and do until our
off the same. They just have a different kind of condition.
Ah, that will end the loop.
So, um four next loop has two variations recharge
substantially different. So we have four next,
and we have for each next. And that was just not the theorize anymore.
Let's just go to the
first of all I have we have that function in Excel. But I have made
the macro that will calculate
factorial all the number
and with me first see that
it's very simple.
called F C. T.
And we defined calculated. This is double
and defined Eye is an integer,
and we at the beginning say, Calculated is one because we're going to multiply it with
value And the value is in
the maximum valued. I mean, the number that we're looking to calculate factorial is in cell a one.
So it says four I equals one to value off. A one
means that it will
change I in every pass through the Luke
from adding one.
we then say calculated equals calculated times I and then we say next I
So at this point, it looks if I is
equal the number 81
and if it doesn't, it goes back to four.
If it is,
it will end the look.
And now we go to
just put in selling to the value off calculated pictorial.
Well, we will say range off, sell a two, and its value equals
calculated and real and the subroutine.
And let's just
and just the
Yeah, and run it
then. So let's hear. Put number five
go to Mac Rose and ran factorial, and we'll get 520 which is exact number.
So why have we decided to put Factorial to be double that? We put it here like we put here like
then we run macro pictorial
and we'll get overflow. It's even bigger than double.
So it's put smaller number, which is, for example, 75.
All right here, 75
and then run this macro
and we get this number. So it's a very big one.
that is the
common knowledge about factorial function. It grows very high as the numbers get higher. So this is how this one works,
and let's just do the other.
So let's put something in these cells
let's first execute the macro,
which is which is called Sells Red,
I would select cells,
and it will paint the cells red if they're hired in certain number and blue. If there
lowered, insist the number.
look at this.
So they're in mentioning, See cell as range
and selected sells his Rangers will
and then we say, set selected sells equal application selection
so selected cells is arrange that consists off dull cells that we have selected in the worksheet,
and this is the function or command within visual basic for applications that will return into this range. Because this is range,
it will return this
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
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.
And if C cell value is greater than 10
then we'll paint the cell red
c cell interior, color function, and we'll paint it visual. Basic blue.
We could have used RGB function, which would give us three parameters, which would paint the cell in pretty much any core we want.
uh, this is just the thing that was done, for example. So we had
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
and it will continue this loop until
it goes through, all off these cells in selected sells range, and then it will
exit Luke and it will go toe ends up.
So ah, this way. We have
done successful for each
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