Excel VBA Basics #10 - Looping through a database and analyzing cells based on criteria

hey how's it going youtubers thank you for tuning in to another episode of excel vba is fun my name is daniel strong and today we're going to dive right into a sample database as you can see I have plenty of items here there's an order date that each has an order number and we're going to focus today on the quantity we're going to have a macro that goes through each line and yeah depending on which line which row we select it's going to take and analyze the toys excuse me that will analyze the category and the last name so if we wanted to know the total number of quantity that people with the last name of Crockett have ordered that is the current category toys it would calculate that up so let's get started I'm gonna hit alt f11 as you can see in my macro here I've already given it a name sum total and I've already declared though DB sheet has a worksheet and I'm given it the appropriate label DB is the name I name this sheet just to make it short and sweet so we're going to begin by how do we create a loop well we're going to do a 4x equals loop so let's begin with that actually we're going to get the last row and we'll call this we can just call lr L R equals and you remember that we're going to take the sheet name dot cells we're going to get the rows dot count comma 1 dot in Excel up dot row and if you have no idea what this is you may want to check out one of our previous in videos in the basic series it tells about how to determine the last row depending on whichever column you want to look at we're looking at column 1 okay we're going to get the last row it's going to say 33 on this database just to test it out let's put a stop right there and hit five if I had a fate to run this line of code sure enough last row was 33 so we're on the right track and we're going to continue on I'm going to go ahead and complete that code here now we have the last row I say 4x equals and we're going to say we don't want to start with Row 1 necessarily we're going to say from 2 to whatever LR is and right now it's 33 and at the end we're going to say next X because we're planning ahead okay yeah I've gone ahead and indented all three lines in my code here so we'll know that they're within these two boundaries okay so the first thing we want to analyze or rather with let's backtrack again we do know that we're going to need to know what the category name is so we'll just call it cat category is going to oh I forgot I'm getting way ahead of myself here one thing whenever you have a macro that you're running anywhere you select you can take the selection dot row or selection dot column and it will give you the number appropriate for that so what we want to do is we want to stick with whatever the row that we're on that were selected on so let's get the selected row we'll call it will call it cell row we could just put SR or something like that selected row is election dot a row and you'll see that that will bear the number 17 since we have 17 and row 17 selected we can use cell row later and that'll be pretty cool so now that we have the selected row we can determine the category and I'm just going to make a little note by putting a comma at the beginning category and we call it cat equals DB sheet dot selves and what is the column excuse me what is the row it's going to be cell row that's a variable that changes and the column index for the category is 1 2 3 4 so cell row comma 4 so that's our category and we need another one for the last name last name ok I made my little note that apostrophe L name we'll call it L name equals DB sheet that sells cell row comma so like the row comma 1 2 3 4 5 ok closing up with parenthesis so let's run it this far if I hit f5 I see that category is clothing for the selected it sure enough if I def 8l name is Smith and for row 17 Smith clothing yep we're good to go now let's run what's going to happen with our loop here we're going to say for each X we're going to say if DB sheet that sells X comma 4 equals category and DB sheet got cells X comma 5 equals L name then enter indent then well then we'll proceed what we're going to do is just put a simple counter we could just say S or C or something C equals C plus and then we need the actual the number of quantity so it'll be column 3 DV sheet dot cells X comma quantity is wrote from column 3 so what we're saying here is and then we'll close it up with an end if ok so every time that this is true every time that the category is the same as our selected category and the whatever that is name is our last name then we want the counter which we've given the variable C is the last counter plus whatever the quantity is that will add up all our quantity let's see what happens well nope nope nope let's see run five six seven zero eight let's just put a stopper here and see where it stops is f5 looks like it stopped us at 14 Oh so on 14 there's closing and clothing in Smith right there this will be interesting f8 C equals C plus D BC that sells x3 is 1067 on row 14 to 6 7 ok so we have a we have a bug we need to deal with here well maybe they don't want us to use C let's maybe C already has a value in Visual Basic let's use something more creative let's say counter equals counter plus that and let's see how they like that yes they like it just fine counter 1067 currently I'm gonna hit f5 ok my marker had it stopped only when the conditions are met when it's true so where do we stop at row 17 that's the selected row anyway counter is going to be counter plus 342 yep okay that looks good I'm going to go ahead and I will put a stopper right here at the end I'm eager to see what happens once it runs all the way to the last row so a counter is going to be 1409 total looks like there was only those two rows that had Smith and clothing in them so that's very interesting at the very end of our thing instead of stopping it let's put a message box that says total quantity for in quote ampersand that's going to join our variable for C 80 that's our category up here ampersand more text so we start a quote total quantity for the category and space quote space ampersand will put the L name and for text total quantity for category and Smith is space quote ampersand last thing we're going to join is counter that was our variable that ended up being 1400 something so let's run let's backtrack and we will go to our message box and hit f8 it says total quantity for clothing and Smith is 1409 this was counter this was cat and this was el name so our variables shine through in the message box let's say let's have some fun with this now I'm going to click on a different row so this one should pick up all the quantity for food and muenster in this whole table let's do that alt f8 brings up our macro list run that one total quantity for food and monster is 1364 well that was boring let's create let's create a situation where see clothing and bush I'm just going to copy and paste that a few areas here and we'll run it now clothing in Bush I'm going to click on this one and hit pick alt f8 and let's run that total quantity for clothing and Bush is five thousand three hundred so it added this one this one this one and of course some of you are saying well you could just do that with a some product formula you could use a some ifs formula and you can actually do that in VBA we're not going to get into that I will give you a little preview though something cool that you can you can use worksheet functions by using application got work sheet function and by the time you type wor K s you can hit tab doct and there's a bunch of worksheet functions like dollar or that some or some if or some ifs some product is real tricky and VBA anyway there's a lot you can do in there there's a lot of built in worksheet functions that you can do in here by saying you know variable name equals and then go to application worksheet function dot whatever vlookup and then put your arguments in there so it's absolutely possible we will get into that later though and some more advanced videos anyway I hope this makes sense what we've done here then thank you so much for watching

Channel: ExcelVbaIsFun

Views: 256,695

Rating: 4.8121791 out of 5

Keywords: Microsoft Excel (Software), macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, vba excel, ms excel, ms excel vba, excel visual basic, microsoft visual basic, thisworkbook, object, vba basics, vba tutorial, visual basic tutorial, range, excel, excel tutorial, excel basics, easy excel, easy vba, easy visual basic, loop, vba loop, analysis, criteria, query, queries

Id: JEVh3hhEiRA

Length: 11min 42sec (702 seconds)

Published: Wed Feb 27 2013