Excel VBA Introduction Part 15a - Find and FindNext

welcome to this YSL tutorial in this short video we're going to look at how you can use the find and find next methods in Excel VBA well begin the video with a quick look at how you can apply the basic find method to a range of cells to find a simple string of text we'll explain a couple of the simple tweaks you can make to the way the find method works so for example you can make your searches case sensitive you can also switch between finding whole words and partial words will also investigate how you can deal with failed finds so when you'll find a method doesn't return any results how you can avoid seeing runtime error messages using object variables and if statements we'll spend a little bit of time creating a basic search system that makes it easy for an end user to interact with and then the final part of the video will be used to investigate the find next method so that you can find all the instances of a value in a range that's a little bit more complex it's going to involve using loops and a couple of extra variables but it's well worth seeing how that works so let's get started let's start the video by explaining the basics of how the fine method works what we'll do is write a simple routine which is going to try to find a particular film in this list I'm going to suggest Ted because it's the shortest one to type and all that's going to happen is that when the routine finds the film is going to select the cell containing that word so let's go into the VB editor to begin with and I've actually already started with a module in there the find method and in here we're going to begin a quick sub routine which I'm going to call find our film now the first thing that you need to specify when you're trying to use the find method is which range of cells you're trying to find something in so I quickly switch back into Excel for a moment hopefully you can see here that the range of cells were trying to look in is cells b3 to be 15 those are the cells that contain the various film names so that's what I'm going to do in a quick simple fashion the first line of code first bit of code I'm going to write is going to refer to range b3 to be 15 just directly by cell reference we'll make this a bit more dynamic and flexible later on in the video if I enter a full stop now I can look for the find method and then open instead of round brackets or parentheses and see the list of arguments we have to fill in now technically there's only one compulsory program for the fine method and it's the what parameter this is simply what it is you're looking for so for us it's the string of text Ted now there are lots and lots of other parameters I can fill in at this point but I'm going to leave these out for now all I'd like to look at before I close a parenthesis is the fact that the find method returns a reference to a range object so if I close the parentheses and then type in a full stop the list of methods and properties I see are members of a range object so what I want to do is simply select the thing that I'm looking for so there we go there's a nice simple procedure just to demostrate how the find method works let's what a stall down on window so we can see what's going on in the background and what I'm going to do is I'm going to tend to run or step through this routine so that we can see what's happening and what I'm hoping will happen is that cell b11 will become selected that's the one that contains the word Ted so if I use the f8 key to begin stepping through when I hit the f8 key on this line I'll be a little bit disappointed I'm just f8 to make sure that the subroutine ends and I've actually started the hobbit' rather than Ted and the reason that's happened is because somewhere in the text in this cell is the three letters T e D Ted so hopefully that demonstrates two quick important things about the way the finder method works by default first of all it's not case-sensitive so I've entered a capital T here and yet I found a lowercase T in the words head here and also that by default fine method doesn't search for whole words it will find the text you've typed in anywhere within the string in a cell so let's go back to the VB editor now and let's see how we can modify those two things let's first of all try to make this search case sensitive to make the fine method case sensitive we need to specify one of those other parameters so if I just click just after the arguments I've passed into the Watts parameter and type in a comma you can see the tooltip appears and it shows me the list of other parameters available now the one that I want is this one a vehicle match case and one way to get to the match case parameter is to continue typing in commerce and wait until the match case parameter turns bold I'll get there eventually there it is match case and if I say true that will make that particular search that particular find case sensitive now that's a bit of a mess when you look at that and try to read it back later on particularly for somebody else trying to read what your code means so rather than using lots and lots and lots of different commas my prefer to do is name the parameters so this means what I will need to name both parameters so in front of the word Ted what I need to do is specify that the parameter that's been passed into is the what parameter so I do that by with with the word what followed by a colon and an equal sign I will then need 1 comma to move on to a different parameter and in fact if I just backspace the word true in the comment for the moment so you can see what happens here if I type in a common now you see that none of the other parameter names are highlighted in bold so this means that I can specify any other parameter that I like so I'll do that by saying in this case match case colon equals and now I'm going to put in the word true or the value true and that will make the match case parameter true so if I restore down the window again so we can see what's happening in the background now once again if I click into cell they want to begin with bring back the VB editor and use f8 setup to step through through the team when I execute this line this time because I'm looking for the word Ted with a capital T hopefully unsurprisingly this time I do get the word Ted with the capital T selected so that's how to make a search or a find case sensitive now earlier we saw that the find method also returns partial matches so when we search for the word Ted and actually return the cell containing the hobbit' because the word Ted was part of another large word in that piece of text we can also do is make the find method only find whole words so let's just do a quick little test here what I'm going to do is insert a new whoa we're gonna have the the Ted sequel Ted to which I don't think I think they're making it but it's not out yet anyway let's pretend it was out so I'm gonna search for the word Ted again using my same find method let's just switch back to cell they want to begin with and with the VB editor on this play if I step through this routine using the f8 key we should see that it searches and finds Ted two and reason it does that is because of the default order that the find method works in works from top to bottom in the range of cells you're looking in well technically actually left to right and then top to bottom if I was searching in this entire list it would go across and then down so anyway it's always going to find Ted two before Ted so what I want to do is make sure that my find method works so that it only finds whole words and again that involves using one of the other parameters of the find method so if I type in a comma here the parameter that I want to use is called a look at so I use the look at parameter full of I colon on an equal sign now sadly this isn't quite as simple as saying true or false as much cases the other look at protons actually requires one of two special Excel constants which unfortunately you don't see what those values actually are the acceptable values aren't displayed so it's a quick way to find out what the acceptable values for the look at parameter are you could click on the find keyword and press the f1 key on your keyboard what that will do is try to launch the context-sensitive help page in Excel actually because I'm connected to the Internet it actually brings up in the MSDN page so anyway here we are here's the list of parameters for the range find method so the look at pronounce a over the ear as you can see can be one of the following Excel look at constants scheming of that an Excel whole or I saw apart so it should be fairly easy to work out from that that the word excel whole will find whole words so let me close down the help page and go back to the VBA editor I'm going to make excel sorry look out equal to excel whole there it is and then let's run the search again starting in cell a1 and then using the f8 key to step through the routine this time we should find is that the cell we select is the one containing just the word Ted not Ted and anything else so there's how to make sure that your searches work both case sensitively and only finding whole words in all of our examples so far we have been able to find a cell which contains the tech we're looking for but I want to see what happens and now have research for something which doesn't exist in our list so let's change the Watts argument so that we're looking for the film called Ted 3 which definitely doesn't exist no list let's then step through the routine using the f8 key and see what happens now ideally what would happen on this line when it doesn't work I'd see a nice friendly message saying something like there was no cell containing that text sadly what we see though in reality is a horrible runtime error message instead object variable or with a blog variable not set I'm just going to hit the end button here to end the routine now technically the problem here isn't to do with the fact that the find method hasn't found what we're looking for the real problem lies in the fact that we've immediately tried to select the cell returned by the find method now when the find method can't find a piece of text that we're looking for it returns the value nothing which is in an intrinsic constant in VBA you can't select nothing so the problem here is Lee with this latch method here's the solution let's take the Select method off the end of the result of the final method which will actually result in a syntax error just for the moment what we're going to do instead is we're going to declare a variable that's going to hold the result of the find method it's going to call it film cell and the type of thing that's going to store is a range because we know that's the type of object that the range are that the find method returns what we can then do in front of the find method itself we can say set film cell equal to the result of using that find now one of two things will result then when that line is executed if I run it now use f8 step through obviously nothing will actually happen but the useful thing is that we don't see the runtime error message what we need to do at this point then is test what the result of the find method is of what value is currently stored in film cell so we can do that using an if statement so let's say if film cell I'm gonna test it currently if it's nothing we do that with a fairly unique syntax if film cell is nothing then I'll simply display a message box I think on screen saying no film was found or no cell was found otherwise else we will select the cell contained in the film cell variable so I say film cell dot select end if and then let's say I'm going to drag the screen across to the right this time so this time of research for Ted 3 again using the f8 key to step through and in fact let me display the locals window as well so we can see this a little bit more clearly view locals window so when the the routine first begins film cell contains nothing it's an uninitialized range variable if I use the f8 key to execute this line because I'm searching for Ted 3 which doesn't exist film cell still has nothing in it so when the if statement runs it finds that film cell is nothing it displays the message box no film was found that's all fine let me just end that subroutine let's this time search for Ted let's go for Ted 2 which has been found made we don't have that cell selected just yet so if I use the f8 key to step through now to begin with again films L has nothing stored in it there's no reference but I run this line this time film cell now has a reference to a range object stored in it so my if statement finds that film cell is not nothing therefore it's going to select the cell containing the text we've looked for Ted 2 and then we go there's a reasonably robust way of using the fine method to deal with what happens if you can't find what you're looking for there are a few more simple things we can do to make this system a bit more user-friendly in a bit more robust the first thing I'd like to do is change how we're specifying which range of cells we're looking in it's currently something looking in cells b3 to be 15 and in fact one of the things I've done during the course of the video having inserted a new row means that one of our films now isn't been incorporated in the search range so what I'd like to do instead is make it so that we search for always every cell from the top of the list and cell b3 to whichever cell happens to be at the bottom of the list there are no gaps in the list so a quick simple way to do that is again using not a variable so I'm going to declare a new variable that's of the routine which I'm going to call dim search range as a range and what I'm going to do is make sure that I set that search range equal to the range of cells I'm looking in so going to say set search range equal to range b3 so that's the top-selling list for by comma and then I'm going to say range B two dots end Excel down now dealt with how you refer to ranges in much more detail in a previous video so I'm not going to claim this in too much detail other than to say the range b3 hopefully obviously is cell b3 range B to to end excel noun it's equivalent to whichever cell happens to be at the bottom of the list from cell b2 so if I select cell b2 and then press ctrl on the keyboard or hold down control and press the down arrow key the cell that I end up in is equivalent to the cell referred to by that string of text so the whole thing goes together select sort of first to any cell between that one and that one and all the cells in between well that means that I can do now is rather than set films L equal to of range b3 to be 15 I can replace that with a reference to my variable called search range and there it is that's the first improvement that I can make the next improvement I can make is in how the user specifies what they're searching for rather than as we have been doing so far coming into the VP editor and modifying this text we could actually use a simple input box to allow the user to specify what they're actually looking for so let's have another variable at the top let's say let's call this film name and this will be a string and we can set the string by using a number box unless a film name equals in the box and I'm gonna specify that the prompt will be type in a film name what I can then do is instead of specifying the what is this literal string of text like I'm gonna place that with a reference to my film name variable I'm going to do a couple of other quick tweaks I'm going to make my match case parameter equal to false and I'm going to set the look at pronounce it to excel parts as well as we're looking for non case sensitive and partial matches again we just closed down the locals window so you can see the whole thing again last little quick thing will do rather than just selecting the film cell let's put int add a little bit of information about it instead so I'm going to remove that line all together and what I'm going to do instead is a message box which will say film cell value and was released on and film cell dot offset 0 comma 1 dot value so that will tell us the release date of any film that we're searching for if it has been found so once I've done all that I can step through the routine let's see what's the easiest way to do this so you can actually see something happening so I begin stepping through the routine the first thing that'll happen is I'm asked for which film I'm looking for so I'm going to type in the word and the something the word dark that's leopard in lowercase letters just prove that it's still working non-case sensitively so I click OK at that point I'm then going to set my search range which would be from b3 down to be 16 at this point and then I'm going to search for the word dark and see if it returns a cell so film cell currently isn't nothing so it's found something so then it should print out the message for us which will tell us that the darkness was released on the 2007-2012 and there it is there's all the information so slightly more interesting system and sort of the basis of a symbol user search facility I guess you could use this for of course it could do with some improvements on top of this but there's a nice little starting point for you so we've created a fairly basic system that lets a user search for a single film in the list but what if we wanted to continue finding all the instances of a word that we've searched for so for example if I run this routine again and search for Ted it will find the first film containing that word which is the Hobbit and unexpected journey but if I wanted to click OK then and find all the other instances of the word Ted in the list what do I need to do well the answer to that is to use the find next method which is a little bit more tricky to use it involves quite a lot of tweaking in this code but let's have a look at how this works to make this system work we simply need to carry on looking for the same piece of text after we've displayed the message for the first cell that we found so let's add a bit of code to the else clause of our if statement after we displayed our first message let's say set film cell equal to search range dot find next so to begin with is actually very very similar to what we did in the first place to find our first film and so we're using find next instead of find now you can only use fine tune X if you've already used fine to find next continues a search that has already been begun by the find method there's one optional parameter of the find next method that we need to specify as well and that's which cell we want to start searching after now if we didn't specify this what would happen is the find next method would then immediately just find the same cell that we're looking at so we need to tell it to continue searching after the one that we're currently on so we actually pass in the same variable of film the cell variable to make sure that it searches after the one that we're currently on now at that point all would happen is that the find next method would happen once it would find the very next instance of the word we're looking for and return that value or return a reference to that cell to the film cell variable so we need to make sure that this continues to happen until we stop finding films so for that reason we're going to wrap these two lines up inside a loop and in this example gonna use a do-while loop so we write the word do before the message box and we write the word loop immediately afterwards I'm going to indent these two or three lines to make it a little bit easier to read so essentially anything that happens between these two lines continues to happen essentially endlessly in fact if I were to attempt to continue running this routine if I start to run it we should see if I search for Ted for example and click OK then it finds the Hobbit and then Ted - and then Ted and then I guess madagascar 3 europe's most wanted and then click ok The Hobbit and Ted - and so on so you can see what the problem is here it's just going to continue to go round and around and around around forever so this one I'm going to press ctrl + break on my keyboard which will interrupt my code so that's control and break some like the key says pause on it so you can press ctrl + pause or ctrl + break to break into the routine then just end the routine so clearly the other thing that we need to make sure that we do is tell the loop to stop looping once it's found all of the instances and we can do that by applying a condition to the loop line now the way that we're going to stop our loop from running is to check whether we've reached the first cell that we found again so the easiest way to do that is to store the cell reference of the first cell that we find and then check each time whether we've gone back to that cell so let's do that by declaring a new variable let's say dim first film cell as a string and what we can do is in our else Clause before we begin the loop we're going to say first film cell we can spell first properly that would help first films l equals film cell dots address so address is the property of a range which returns the cell reference in the loop then we can add a simple condition to the end of the loop so we can say loop while film cell dots address is not equal to first film cell so each time we we we get to the end of the loop it's going to check whether the new film cell that we've just detected is not equal to the original cell address that we stored and as long as that's not true the loop will continue looping so let's just run this one more time now if I use the run button just approve that it will work first of all some message for Ted again so get the hobbit and then Ted 2 and then Ted and then Madagascar and hopefully at this point when I click OK we're not going to then see the Hobbit film again if I use the f8 key to step through this one hopefully we can see if I stop gonna film now I'm going to Ted what just one more time and then click OK I can see that we cyan't find the first cell and I'm going to store the cell reference in this first film cell variables hopeful you can just see there that it stores the reference dollar B dollar 7 so each subsequent time now that it displays a message or finds a new film it's going to check whether the film cell address so this sum is B 11 which is not equal to B 7 meaning we'll loop around back to the top and then continue showing the message and the next film cell is b12 which is not the same as B 7 so it continues and the next film cell is B 13 which again is not b7 so the next time it has actually found again the Hobbit film so it the find next method has found the same cell again but now we're checking that B 7 actually is the same as B 7 so the loop ends the if they man ends and then finally so does the subroutine so there's the basics of using the fine next method hopefully you found that useful if you've enjoyed this training video you can find many more online training resources at ww-why 0 UK

Channel: WiseOwlTutorials

Views: 137,835

Rating: 4.9375 out of 5

Keywords: Microsoft Excel (Software), Visual Basic For Applications, find, findnext, loop, do, wise owl

Id: _ZVSV9Y7TGw

Length: 22min 58sec (1378 seconds)

Published: Tue Oct 07 2014