Excel VBA FIND Function (& how to handle if value NOT found)

in today's excel vba tutorial we're gonna take a look at using the find method this is the same thing as doing control F in excel or going to home find and select you've probably noticed that when you use the find dialog box and you search for something Excel finds your value really fast it's almost instant that's why using the find method can be considerably faster than looping through many cells in VBA let's see the arguments that we need for this method first off its expression that find and here on the Microsoft website it says that the expression is a variable that represents arrange objects so you can use the find method on a range and this can be all the cells in your worksheet if you do cells that find or you can restrict it to a specific range the only argument here that's required is what you're looking for you can see the rest are optional but even though a lot of these are optional I do recommend that you set some of them now let me explain why for example the looking argument decides where you're looking for your variable if you're looking for them in formulas values and comments now formulas and values are very similar except that formulas look inside values but they also look inside the formula text to look for the value that you're looking for notes is the same as Excel comments and that's basically the comments inside your Excel worksheet the reason setting this is important is that if you don't set it the default reverts to what you had selected last so imagine this you had opened your Excel spreadsheet and you were looking for a specific comment and you use the normal excel file dialog box and you change that drop-down to comments later you wrote your VBA code and you didn't specify what it should pick under look in you just left out this argument it's still gonna look inside the comments so even if you just want to look inside the values or formulas it's gonna look inside the comment because that was the last setting that you had same logic applies to look at this one decides if you want to look for a full match or a partial match and again if you don't set this specifically in your code it goes back to the setting that you had last another useful one is match case so if you're ever looking for a case sensitive match you can set this to true the default of this one is false another one that comes in handy if you're looking for many matches is the after argument and that's something that we're gonna take a look at in the next lecture in this lecture we're gonna take a look at the simple version of find and just look for one match but before we switch to our Excel workbook let's just think what type of variable we need to save the finding in that variable is a range object okay so we need to use the set keyword when we use the find method now let's switch to excel and so for this problem here what we need to do is to find the article code for this entity this entity here is basically a company ID from here so we have our drop-down we select a company from this list and when we click on this button one match it should give us the article code that's associated with this okay in the next lecture we're gonna take a look at the many matches because as you can see some of these have multiple instances and they have different article quotes but let's start simple now let's bring up the visual basic editor and see how we can set this up so alt F 11 I've already created a new module to practice find here and created a new sub procedure for this example okay let me collapse this so that we can zoom in better as a first step let's create the variable that we need to keep the results of the find function so I'm gonna dim comp id as range now because this is a range we need to use the set statements so company ID equals first was expression dot find and that expression was arranged right so I could do cells that find if I didn't know where that comp ID was like if I didn't know for example if it was in column a if it could be anywhere I could just to self-thought find or in this case I know that it must be somewhere in column a so I'm going to restrict it to column a you can of course be more specific and also restricted to like a seven in this case to a twenty but I'll just go with the column in here dot find now let's fill up the arguments you have the option of using the comma to skip some arguments and just jump to the arguments that you want to fill or you can specifically define them here then you don't need to worry about the orders in this case I'm gonna specifically define each one where is our value that we're looking for it's in range B three races B 3 dot value next let's specify the look in I'm gonna set back to excel values the look at should be Excel whole because I want to look for a perfect match close bracket let's just see what we get here by adding this to the watch window and just pressing a fade on this code okay I see the value associated with this and it must be showing this cell so I can double check by changing the property of this to address and that's a eight right so it actually jumped to a eight I'm not interested in getting a eight back here but I'm interested in getting eh back here so what I can do is to offset the location of this of a eight by four columns I'm going to be writing that results in C three right so I can already say range c 3 value equals comp ID dot offset how many rows do I want to offset nothing how many columns for and when I get the value of that okay so let's just play this and I get 120 and that's the right one what happens if I look for something that doesn't exist what do I get back I think I put an entity here it's this one that doesn't exist on this list okay let's do f8 on this it runs into a problem with the address let's take a look at the value of this just going to delete that its value is nothing that's why it runs into a problem because I've defined it as range but it's giving back nothing which is not applicable for this object type so I need to make an exception using the if statement if not comp IG is nothing then it should do this otherwise else it can give a message saying company not found hidden end if in vba the not keyword is used quite often because it's much easier to say what something isn't than what something is and in this specific case we know that it's either nothing or it can be different ranges so it's very practical to say if it's not nothing then give us the value otherwise give us a message that the company isn't found let's test this company not found it looks like it's working and just quickly going to assign the macro to this button and let's just run some tests here let's pick the de company which is right here and I get 107 okay now if I pick this PL 1 I get company not found but it still keeps the value of the previous one so what I should actually do is to clear the contents of this before I run the macro ok so now it's good it deletes that if I select something else it finds that in case you'd like to find out more or you'd like to learn VBA in a structured way check out my complete Excel VBA macros course you're going to find the link below this video you're also going to find it in the cards provided or just go directly to X al plus comm slash courses thank you for watching see you in the next video [Music] [Music]

Channel: Leila Gharani

Views: 128,820

Rating: 4.8944764 out of 5

Keywords: VBA FIND, excel vba find function, using find and findnext in vba, vba how to find a string in a cell, vba find next, vba find function not working, vba find function syntax, vba find return column, vba find function if not found, vba lookup, vba find text, vba find what, vba find records matching criteria, XelplusVis, excel vba tutorials, Leila Gharani, Excel 2016, Excel 2013, excel macro tutorials for beginners, excel vba, Excel 2010, Excel for analysts

Id: nV_oDWJccu8

Length: 9min 33sec (573 seconds)

Published: Thu Jul 12 2018