EAF #37 - Excel VBA Loop to Find Records Matching Search Criteria

welcome to this excel video in my previous video I used a complicated index function to pull out any Records which matched a particular criteria today I'm going to using the same data set accomplish the same outcome but with a little piece of VBA and what's neat about this piece of VBA is it's something that I know when I first learned and I copied it and pasted it in suddenly the note file and copied and pasted it back into code windows many times and used a very effectively so it's a simple loop which will go through each row in the data set and pull out anything that meets criteria so to do so I am going to go into the Developer tab and into Visual Basic so if you have a lot of development app you need to click on file options customize ribbon and you see over on the right hand side here developer needs to be checked in my case it's already checked but if you can't see the Developer tab that's what you need to do so into Visual Basic I go and here's our file it's called eaf 37 i'm going to click insert module modules basically like a blank sheet of paper where you can just start writing code so what I want to start with is a name so I'm just going to call this some basically means it's a sub procedure or a small procedure I'm just going to call it find data and so these are the three things that I want to be able to do during this little piece of code to clear some variables to clear out any old search results and then I have to find some wrinkles the match the criteria and put them in the right place so firstly I wanted to clear some variables and first thing I wanted to clear is a variable called ethnic name not absolutely necessary but it allows the code to look a bit clearer and easier to read later ethnic name as the name of the athlete will select with a drop-down box and it's the one that we want to check the entire database against next one I declare is something called final Rome final road is at the moment Rome a the agent our data set was basically the last row of data that we have now the reason for declaring this variable is it basically stops that the code from having a search through all the rows and the entire spreadsheet so there is over a million rows we don't want to have the code run slowly because it's having to do the entire sheet we just wanted to stop when we get to the final row the last one I wanted to clear is I now I was just a row encounter I is going to allow us to step through each row Row 1 Row 2 Row 3 Row 4 etc so there are our our variables that we've declared now we can get into step number two which is clearing out the old search results so if I just get out of the VA for a second and go into Excel the outcome that we're searching for here is that anything that meets our criteria will be pasted here so if we get down to a thousand rows there may be quite a few data sets for a particular individual so let's say starting in p5 and going down to Z 50 let's say that's the space that we want to clear out so we have to refer to sheets the sheet is called data then we have to refer to the range and the range is something like that you could go further down if you like but we'll assume that it's unlikely it's going to be more than 45 Ripple's for any given person anything this year is the clear contents command to get rid of all that information here I'm not giving it any formatting then we might have put in place all right so that's number two taking care of before we start the code what we want to do is just add some information to the variables that we have to clear earlier so we want to tell it that so what we'll do is tell Excel tell VBA we're athlete's name is we have searched for so as soon as you click the button to start this piece of code it's going to look in cell P 2 and set the value of P 2 which in this case is Donald Trump and set that to the variable athlete's name now what we want to do that the variable final row is I'll just go to excel what we want to do is we've got two ways of doing this we could go to cell a1 and the ctrl down arrow and then will take us to the last record and our data see now that method works just fine other than it feels got a brand-new database with low rows of data room if you just have the title rolling no data hidden control down arrow it will take you down to Rome 1 million and 48 thousand so we don't want that but it has identified that there is potential for a little bit of an error if we do this so what you do is go to the bottom of the data set will go someone well below where you expect the data set to get to for example on this easily lobs to take me to 19,000 and hit control up there so rather than starting at the top and doing control down arrow I start somewhere below it and go control up arrow so if I replicate that process in VBA it looks something like this go to sheets data range a 10000 hit the control up arrow and record that the row number as our variable for final row so we're going to get into writing a piece of code now that actually does the the real business of this process so what this for loop does is for all rows from Row 2 to final row it looks at the following test if whatever is in a particular row we're searching it so that's what this es cells I 1 that means if the row we're looking in if column 1 equals earthly name doing what we want to do is copy everything from column 2 to column 12 and then paste it somewhere else so we're we want to paste it is over here and initially the first record that we find that matches our criteria we're going to paste here the next one we're going to paste here and next one we're going to paste here until it's no more so we can't just go to a particular row and hit paste we have to do the same process that we went before to find the final row we have to start at the bottom and hit the control up arrow so I've put a little marker here and this is what the code is basically doing each time it copies our record it goes here goes control up arrow down 1 and then paste it so let's look at that code what it does goes to range P 100 does control up arrow then goes down one row and across zero columns and then does it pay special and that just keeps that the formula and number formats okay when it does that it goes to the back to the data set and lots of the next row so it goes through all the different rows that match the criteria and then it ends so what I want to try and do is run this code and see what happens so from anywhere inside the code I can just click the play button and there we go it finds what we're looking for now what I tend to do it because it's got the last row selected here so might just go something like that so range P to select basically puts the cursive back out the athlete selection place which is here so the final step that I want to do is insert a little button let's say I can put it here you can allocate a button to a macro this works only got one so it's easy right click on it get it text find data so if I change Donald to Franklin and hit find data it works just fine so let's try Alex Douglass piece of cake it's our little piece of code is working just great it's searching through all of the records in our data set and pasting them one at a time into here so thanks for coming past looking a little bit of VBA a nice follow up from the last option as it demonstrate so there's lots of different ways to do things and another little journey into VBA for you see you next trip

Channel: ExcelTricksforSports

Views: 364,934

Rating: 4.8765273 out of 5

Keywords: John Lythe, johnlythe, Impact Score, impactscore, Excel, Microsoft Excel, Microsoft Excel (Software), software, tutorial, Learn Excel, 2010, Excel 2007, Excel 2010, formula, formulas, Functions, MS Excel, Spreadhseet, spreadsheets, technology, Workbook, Worksheet, Lookup, tips, tricks, tutorials in excel, VBA, Loop, For Loop

Id: QOxhRSCfHaw

Length: 11min 35sec (695 seconds)

Published: Sun Jan 20 2013