### Intermediate Excel

Course
Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

### Video Transcription

00:00
Okay, let's continue
00:01
this macro for the sales competition.
00:06
We stopped here, and we have found in this loop
00:11
the maximum value for
00:13
ah,
00:14
maximum sales price for chair and max themselves for rice for table. So we have these two values,
00:21
and now we're going to look
00:24
who meets these requirements.
00:27
So we're going to do again one do loop. And also it's going to be until
00:33
range of A and I is equal.
00:36
Nothing. Because then we have gone through all the files
00:41
and, uh,
00:44
yeah, before that were again going to set the counter to two because we don't need it anymore.
00:51
I mean, we we we don't need it here so we can use the same counter. So I will be counter again.
01:00
And now we're going to do through grow
01:04
and see if the sales price
01:07
is equal to Max Price chair.
01:11
So let's say if
01:15
mix
01:17
price
01:18
chair
01:19
is equal
01:22
to range
01:23
off
01:26
the price is Aaron called e so e
01:30
and
01:32
I
01:33
So if it is equal,
01:36
then what we do?
01:38
We
01:38
then
01:42
ah,
01:42
see,
01:45
we put
01:46
here
01:48
the name of the person.
01:49
Now, this is G three,
01:53
and we have to set first the counter
01:57
for
02:00
share. So here we will say, S C C equals three.
02:07
And as C table
02:10
equals three as well.
02:14
So
02:15
we're going to this look and then we say
02:19
that
02:20
range
02:27
off
02:31
looking for chair. So it's for chairs G.
02:36
So it's G
02:38
and
02:40
let's see
02:45
equals
02:46
you're looking for the sales person. So its range
02:52
out off
02:54
a
02:58
and I
03:01
and stopped value.
03:08
We could.
03:09
But here that you also just to make sure that this is
03:15
fully proper
03:19
and then we just do.
03:22
And if
03:24
so, we have closed this one,
03:28
we have no else. So if it's not,
03:31
it can mean that the air in the row where the prices for table
03:36
or that it is so we're not going to think about it. And now we're going to copy this room again because we don't need to type everything.
03:45
And here we're just going to change with me be changed. So
03:50
Ah,
03:52
yeah, If Max Price stable, is it
03:55
value off e
03:58
I? So it's a
04:00
this and then
04:01
the range is
04:02
eight.
04:04
Sorry.
04:08
And
04:10
now it's a CD,
04:14
and it's equal
04:15
to range.
04:19
Range
04:23
off.
04:25
Hey
04:26
and
04:27
hi.
04:29
Nice that Sorry good value.
04:32
Also, what we forgot here
04:35
is to now increase the counter off sec. So if this has happened, we added something.
04:42
04:44
one,
04:46
two
04:46
sec
04:51
and we're going to do that
04:55
the same here for a city. So a city equal the city Waas one
05:03
and we're going to do.
05:05
And if
05:11
and just to make this fully
05:15
trouble,
05:16
I'm going to do this.
05:18
So we have closed.
05:21
Well, you check this line.
05:24
And so if his metric
05:27
it's put here the capital aged just
05:30
you ever think
05:31
nice
05:33
and what we're doing now
05:36
is we're saying I equals eye plus one. So we're going to the next
05:42
row,
05:44
and then we're going to
05:47
loop
05:49
until
05:54
the same thing here.
05:56
Sorry,
05:58
We're just going to copy this
06:03
and who would present there?
06:05
And we have closed us.
06:08
So we have
06:11
gone through row after row with this loop
06:15
we checked. If
06:17
the line
06:18
we're in this line, the price is equal
06:21
to make surprise chair.
06:24
And if it is, we'll just write that name into
06:29
the
06:30
wieners off the chair.
06:32
And then
06:33
again,
06:35
we're going to do that
06:38
for
06:39
the table
06:40
because in every line we can have either chair or table.
06:44
We still do this
06:46
checkups. But we know that it cannot happen that in single line we're going to have
06:53
more than both entries
07:00
entered in Combs, G and H.
07:03
So
07:05
that's it?
07:06
We're going to close
07:11
***
07:12
while actually, we would save it.
07:15
So we're saving this
07:19
and it should work.
07:21
So we're going to close it.
07:25
We've been too close. We should basic
07:27
it becomes this
07:31
and we're going to sign macro, which is going to be our micro winners.
07:36
Say aye.
07:38
And we just
07:41
quick in here
07:43
and you see, we have a
07:46
something wrong
07:47
and let's just see what's wrong.
07:55
Yeah, I have placed here s CD instead of a C t.
07:59
So it's a typo
08:01
and we're going to change it.
08:03
And
08:05
you could save change.
08:07
So everything else was check sec. Is cssc
08:13
a city? A city is a city.
08:16
It's closed this
08:18
into hotels that
08:20
the beggar is closed.
08:22
Let's click again.
08:24
And
08:28
this here We also have a
08:31
air
08:33
check again. What? The air is
08:37
so
08:39
overflow.
08:43
They're looking if a
08:46
Okay,
08:50
yeah,
08:52
we have for gotten puts the range here
08:56
and now it should work perfectly
09:00
because it went on and on and on because
09:03
A and I
09:05
should have bean false or true and
09:09
that that's that's not happening. So this is not correct. And we should put here well, you
09:15
as well
09:16
as
09:18
well as
09:20
here.
09:22
Just to make this fully correct
09:26
to see type something, you make a few mistakes quick here.
09:33
Yeah, Okay.
09:37
It's this.
09:39
Save it. So this is how this debugging process looks.
09:43
And let's close this
09:46
09:48
and everything Is that so if we delete this just to make sure that they're working is working fine.
09:54
So we have just been down here on Mike here,
09:58
and yes, these are the pillars,
10:01
and it works perfectly. So this is how you make a macro for something that looks rather complicated. But when you look at the code, it's quite simple. It just needs in practice.
10:13
Now, just one more thing for this module. I'm not going to give you any,
10:20
uh, exercise files. This has been exhausting enough,
10:24
and I believe that you can now do your practice by yourself, just going there and write some simple Mac Rose and get
10:35
acquainted with special basic for applications, excel and how it's done.
10:39
And thank you for listening

### 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

Milan Cetic
IT Security Consultant
Instructor