1 00:00:00,450 --> 00:00:05,220 We're going to use our newly found skill of using loops and we're going to use it with our first initial 2 00:00:05,220 --> 00:00:11,000 database where we have the names and the titles the dates of the sale and the sale amounts. 3 00:00:11,010 --> 00:00:17,730 So what we're going to do now is we're going to basically build our first report one step at a time 4 00:00:18,190 --> 00:00:20,480 all development to get into the Visual Basic Editor. 5 00:00:20,490 --> 00:00:24,750 Let's do a simple loop and what are our starting parameters. 6 00:00:24,750 --> 00:00:26,840 We know it's going to start on two. 7 00:00:26,910 --> 00:00:35,160 So our FOR FOR NEXT loop is obviously going to be for X or for whatever equals to all the way to whatever 8 00:00:35,160 --> 00:00:36,590 our last row is. 9 00:00:36,840 --> 00:00:41,660 So we need to combine the skills that we've learned so far we need to get the last row and then we'll 10 00:00:41,670 --> 00:00:43,710 reference that as the last. 11 00:00:43,710 --> 00:00:50,880 Instead of putting typing 10 to 10 we'd put from 2 to whatever our variable is that contains our last 12 00:00:50,910 --> 00:00:51,840 row. 13 00:00:52,320 --> 00:00:55,390 So all this combined can bind those two skills. 14 00:00:55,560 --> 00:01:03,030 We're going to create a new set we're going to call it my first report and very simply for the first 15 00:01:03,030 --> 00:01:07,440 one we're going to do it in a message box in the next video we're going to show you how to do that in 16 00:01:08,520 --> 00:01:09,900 an actual different sheet. 17 00:01:09,930 --> 00:01:12,980 So you can have a printable report very easy. 18 00:01:12,990 --> 00:01:14,890 So first things first. 19 00:01:15,150 --> 00:01:21,270 Let's go ahead and let's go ahead and get our last row. 20 00:01:21,320 --> 00:01:22,510 So let's go and get them. 21 00:01:22,590 --> 00:01:27,180 Now let's call it the last row equals and we're going to say 22 00:01:30,210 --> 00:01:34,180 we're going to say cells and we're going to use rows not count. 23 00:01:34,190 --> 00:01:37,790 Comma 1 using the in function. 24 00:01:38,040 --> 00:01:42,170 We're going to go x l up and we're going to extract the row. 25 00:01:42,180 --> 00:01:50,620 That's our last row and then we're going to say for X equals 2 to our last row 26 00:01:53,570 --> 00:01:58,430 you are next X hit up hit tab and we're ready to go. 27 00:01:58,740 --> 00:02:07,100 So what we want to do in this case I want us to do a report for anything over two hundred dollars. 28 00:02:07,230 --> 00:02:12,990 So that's going to be analyzing column for let's see if 8 and get started. 29 00:02:12,990 --> 00:02:14,810 So last row we see is 11. 30 00:02:14,850 --> 00:02:15,630 That's good. 31 00:02:15,780 --> 00:02:20,840 X is going to be 2 all the way to 11 because that's our last row variable. 32 00:02:21,150 --> 00:02:27,540 Before we begin let's play this out we're going to say if number the If Then statement you can say if 33 00:02:29,920 --> 00:02:41,710 cells using RHO X which is going to be to first if cells row X and column 4 which is the money is greater 34 00:02:41,710 --> 00:02:50,470 than or equal to 200 bucks then in hit enter tab and other enter in I'm going to shift tab and type 35 00:02:50,540 --> 00:02:55,550 and if that way it won't give us errors later. 36 00:02:55,570 --> 00:03:02,230 So let's just take a look here once we initiate our for next loop it will actually give X the value 37 00:03:02,230 --> 00:03:02,930 of 2. 38 00:03:03,100 --> 00:03:09,520 And so now we'll have to call him for which is cell D for the value of 482 is that greater than equal 39 00:03:09,520 --> 00:03:10,850 to 200 Yes. 40 00:03:10,870 --> 00:03:14,400 So that would be our first yes to this question. 41 00:03:14,590 --> 00:03:19,710 So let's get maybe their name let's get their name in a message box. 42 00:03:19,750 --> 00:03:22,870 So we're going to say let's say my message. 43 00:03:23,020 --> 00:03:24,280 That's another variable. 44 00:03:24,280 --> 00:03:28,540 My message equals whatever my message was. 45 00:03:29,400 --> 00:03:40,020 As well as am whatever is in that cell only We're going to use cells the current row or we're going 46 00:03:40,020 --> 00:03:41,600 to take. 47 00:03:42,360 --> 00:03:45,600 We're going to take column one which has their name. 48 00:03:45,720 --> 00:03:47,620 So just to show you what that does. 49 00:03:47,670 --> 00:03:52,890 Let's see it's going to be empty right now but we're going to combine that with Heraldo which is his 50 00:03:52,890 --> 00:03:54,090 name. 51 00:03:54,090 --> 00:03:57,860 Then the next one let's see that's two and a 57 still greater than 200. 52 00:03:58,050 --> 00:04:02,070 So this would be her older Heraldo and Makow. 53 00:04:02,070 --> 00:04:03,170 So now will say Heraldo. 54 00:04:03,180 --> 00:04:07,040 MCCALL Well I don't really like that you could have a space. 55 00:04:07,080 --> 00:04:12,210 But what I would prefer to do is to add a new line and I'll show you how to do that. 56 00:04:12,420 --> 00:04:16,990 You say ampersand space. 57 00:04:17,050 --> 00:04:20,020 VB new line. 58 00:04:20,040 --> 00:04:27,060 Add another ampersand to join that and the VB new line is a built in variable that tells it to create 59 00:04:27,090 --> 00:04:29,460 a new line in the message box. 60 00:04:29,460 --> 00:04:33,860 So if we start over it Effie. 61 00:04:34,250 --> 00:04:39,640 So first we have Heraldo then we have Heraldo new line in Macau. 62 00:04:39,640 --> 00:04:44,980 It doesn't look like it but if I hit F5 and it ran. 63 00:04:45,140 --> 00:04:50,080 So at the very end when you're done with all your looping and outside of the loop we're going to say 64 00:04:50,090 --> 00:04:59,300 message box and it's going to be my m s g Now my m s g is just a random word you could put the word 65 00:04:59,300 --> 00:05:04,670 blah here and here and here or any word you want it for that variable and it would do a message box 66 00:05:04,670 --> 00:05:06,640 or whatever is contained in there. 67 00:05:06,680 --> 00:05:08,290 So let's stop it right here. 68 00:05:08,450 --> 00:05:15,330 Let's hit F5 runs all the way to the end and let's show you what the message box looks like her although 69 00:05:15,460 --> 00:05:21,950 McCowen June Dan Amy Terisa and Fernando those are the only ones that are above 200. 70 00:05:21,980 --> 00:05:26,660 So if you want to make that a little bit more strict you could see who all has more than 400 by doing 71 00:05:26,660 --> 00:05:27,870 this. 72 00:05:28,100 --> 00:05:38,330 And let's hit F5 now Heraldo And Dan in let's see Heraldo you more than 400 and Dan more than 400. 73 00:05:38,360 --> 00:05:40,260 So that is correct. 74 00:05:40,450 --> 00:05:43,700 In the next video we're actually going to show you how to make this a little bit more dynamic using 75 00:05:43,700 --> 00:05:44,830 an input box.