4.5 Using Complex Macros with Real-Life Problems Part 5
8 hours 33 minutes
Okay, This is the final example you're going to see in this module.
And the data you see represent sales
collected in ah when the Friendship Store,
where manager has decided to organize a sales competition. And unlike regular competitions that they're usually about who sells the most or who makes the biggest margin
here, he's going to
prizes to those salespeople that achieve highest discount. Sorry, Lo. Is this count or high selling price on to selected products? One type of chair in one table.
Why highest selling price? Because this is a wholesale store. So people come in and say I want to buy 20 or 50 or
100 chairs and how much this country going to give me
and they agree on a discount. And then, ah,
it gets ah,
recorded by somebody from an administration
entered in the stable
and at the end of the competition they want to extract from this data. So what you see here is just on example.
It can be hundreds and hundreds of rose.
They want to find who got the best selling price high selling price for chair and high saying place for table.
If there are more sales people who
ah, get go to the same selling price for
either of these items. They will simply share the
cries, which is in money.
um, it's very simple. Data is quite clear. Have sales person.
We have product that is selected from the dropdown list.
Then the list price is calculated automatically because we know it's 30
a chair and 85 table discount is entered by
the administration person
and price at which the product is sold has been calculated automatically with this simple formula.
So because we can have more than one,
person achieving the best selling price for chair
and for table, there can be more than one entry here, and Manager wants all of them listed here
these separate comes for chair and table,
and he was just a click on this button
and get the results so
she wants to do it in front of
uh, we cannot use look up functions. We have to write the macro
and macro. I'm going to write for this
I have nothing prepared, so I'm going to write it from scratch. And if I make some mistakes.
Uh, it's normal.
Even if you're really experienced, you probably make some mixed aches, even or or it can be typing or even in some logic, and then you have to correct it. So let's ah
not expect that this will go without any hiccups.
Um, if he does perfectly, but I want you to see how the process of creation looks.
So let's go to Mac Rose. Let's enter
the name of the macro is going to be winners
and let's create it.
And we get with open,
so we're first have to define our variables.
So we're going to have first,
or, let's say, just mix
price for chair
and well defined it. This Ah,
this symbol number so single
Then we're going to need some counters
and let's not give them any complicated names.
So let's get say we're going to have a counter that was going to go through cells is going to be a rosary is going to be I.
So it's gravity in future,
and then we're going to have a counter for,
how many sales people have reached the
maximum selling price for boat.
So is goingto this
and it's going to say,
I would just say sales counter
table also his integer.
So this one is basically going to count how many people
achieving the best price
so that we can,
uh, ads or move our table one row lower when we put their name there. So it's also going to be integer,
that's pretty much it at the beginning. If we find out that we need some
other variable will define it later, doesn't
intrude with the
writing off the code itself.
So what is the first thing we do?
Let's find the maximum price for chair and maximum price for table.
So it's going to be do loop, and this is going to be do look, until so we're going to go through all the items until we reach that there are no more entry. So that's an empty
what are we going to do
we're going to ask
first of all, yeah, before that,
we're going to set maximum selling prices
for chair and for table 20
because this is definitely a minimum and everything has to be
hired and it's so it's zero
and let's start with the
So we're going to say if
and then let's look here.
So we're going to look a TTE
this price here.
So it's called E and Colby
and of course, yeah, the counter.
We're going to
this happens when you don't finish line.
So the counter
is going to be set at
and then we're going to see
so, we're looking at column E
and it's going to be a range off
So we're going to start with E too.
you have a typo
Max Price share. Sorry. First we're going to look if
e off e and I is higher,
then Max Priest chair,
next price chair
is going to be
to this number.
So it's the newest highest number for
prices of chairs
we can put here that well, you a cz well,
and here we can put it
well, you, But we don't have to because It's a default value or default extension,
but let's make it
So here after then
we're going to
and we're going to do, and if
and again. And
we checked this one
and then we're going to
copied the scene,
then here is going to be table.
here we're going to have express for table.
And here is well, is going to be next press for
and everything else is the same. So we're looking for the
price. So if we are looking, if B
two is equal to table,
two is bigger than Max prize stable, then express table becomes this. So
we close it
and then we do
and then we're looking at the first column. So range
is equal to nothing.
And here we just forget to do one thing
when to say I is equal, I plus
So we're adding one to the counter and we're looking gift. The next row is empty. If not, we go back and do it all again,
and we have finished this first loop
and let's continue in next video