Excel VBA FIND Function (& how to handle if value NOT found)
Вставка
- Опубліковано 3 сер 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Elevate your Excel skills with this in-depth tutorial on using the VBA FIND method. This guide is perfect for anyone looking to streamline their data search process in Excel using VBA.
🎓 Get access to the full course here: www.xelplus.com/course/excel-...
🔍 What's Inside:
▪️ Understanding the FIND Method: Learn how to use Excel's FIND method in VBA, similar to using Ctrl+F or the Find and Select option in Excel, but much faster and more efficient.
▪️ Arguments Explained: Dive into the key arguments of the FIND method, including expression, what to find, look in, look at, and more.
▪️ Importance of Argument Settings: Understand why setting arguments like 'look in' and 'look at' is crucial, as default settings revert to your last manual find settings in Excel.
▪️ Creating a Range Object: Discover how to use the SET keyword and create a Range object to store your FIND results.
▪️ Practical Application: Apply these concepts in a real-world scenario where you find the article code for a selected company ID using a dynamic VBA code.
Quickly learn how to use the VBA FIND Function. What parameters are required. How to look for partial matches or for complete matches (whole word). Also if VBA should search in the cells, formulas or comments. I also show you how to handle cases if VBA FIND doesn't find a value. So in cases where your text or value is not found.
You'll want to account for this, because in case your text or value is not found, you'll get a VBA error. In order to avoid the error, you can use the VBA IF statement. This ensures that if a value is not found, you will not end up with an error, instead you can decide what you'd like to do (in the video I'll show you how to create a custom message box, informing the user that the value was not found).
★ Links to related videos: ★
Check out the full playlist: • Excel VBA & Macros Tut...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#ExcelVBA
Get access to the complete Excel VBA course here 👉 www.xelplus.com/course/excel-vba-excel-macros/
"If Not CompId Is Nothing Then" -- this is the content I came for. Couldn't find it anywhere else! Thank you! This is the second time I've found explanations here and nowhere else. Your presentations are very clear for newbies like myself.
Thank you Leila, You are a really nice teacher, I like your tutorials and your way to explain everything is a really good experience to learning from you.
Thank you for the video, your Excel VBA tutorial is one of the best VBA courses on Udemy. It's so well organized, professional and content rich which makes it absolutely enjoyable and useful for a solid career in Excel VBA programming.
Thank you very much! I'm really happy to hear you find the course valuable :) Thank you also for your support of my tutorials here.
Not many people explain as well as you. The examples are really very good and they adapt to the labor needs. I do not know where I was looking before I had not found such a good channel. Congratulations! And Tksss 😀
Thank you so much Nanzyz! I'm very glad you find the tutorials easy to follow :)
Thank you very much for the VBA FIND Function usage, Leila : ) it was awesome it saved me lot of time in retrieving the data Hats off to you
Excellent way of avoiding an error if nothing is found.
Exactly the kind of macro I was looking for. Thank you very much.
I lost interest in learning vba and excel functions but thanks to your channel here, it makes it interesting and motivating. Thanks a lot.
Thanks, great explanation of the IF NOT, IS NOTHING syntax. Makes sense to me now.
Thanks. Yes - this syntax confused me at the beginning when I was learning VBA - I thought others might have the same problem. Glad the explanation helped :)
Really great tutorial, also very soothing voice. Thank you so much!!
Thanks for the VBA FIND Fun, Leila : )
You're very welcome Mike :)
Hi Leila, I'm new to Macros but this video, was so made so easy that I was able to make my code work.
Awesome tutorial!
I could avoid a nested for loop reducing runtime greatly thanks to your tutorial.
Thank you!
You're welcome!
I love the VBA videos, so helpful. Thank you!
You're welcome - I remember you asking for them :)
Thank you so much for your good and lean explanations
Leila, thanks for FIND video :). Extremely useful
Wow.. Wow... Thank you so much Leila...!! As you promised you did it.. Thanks for considering my request to make videos on VBA. Thank you we expect more from you...!!!
;-) You're welcome. I'll try to alternate between usual Excel and VBA. If I manage, I'll get two videos in a week - let's see how the summer goes.
thank you Ms. Leila in fact i like this so much because I'm going to use it in my new excel software thanks again.
Thank you Ismail. I'm glad you find it helpful.
Very useful VBA, and very well explained, thank you so much!
You're very welcome Carlos. Glad you find the video useful.
Hello Leila, Kudos to you and your knowledge about excel. The way you have explained this video is what I was looking for. Was expecting something from MS to do :)
I was also wondering, if you can share the link on how you calculated many matches in this example ? Thanks in advance!
Elegant solutions with pleasant, informative explanations. What more could we ask for? Thanks.
You're very welcome Michael. Glad you like it.
Thank you for helping clarify some syntax for me!
I was 1st like this time round. All ur vids are so helpful and easy to understand
Thank you Naadiyah :) Glad to see you here.
Hi Leila ,
Thank for sharing the tutorial , it is very helpful to everyone , need your help , can you please share the tutorial of INDEX and Match function in VBA using one sheet 1 to sheet 2 .
Leila, thanks for FIND video :). Extremly useful :)
Thank you Malina :) Fully agree - I use it in 90% of my VBA files.
Leila, you are awesome 💌
Very useful, thank you.
Last but not least. Thank you. Great video.
I mean you are seriously great!
Nice Teaching
Good Explained ! Waiting for what next...
Glad you like it.... VBA is coming again soon...
Leila, as ever a very clear explanation. I wonder have you covered (or could you :)) using find (or similar) to move around cells in a range where you are looking for numerical values not equal to a set value? Find's With clause doesnt like "1"
THANK YOU A LOT ! 👍
A professional video tutorial, thank you! Do you have a similar for Gantt Project in Excel?
I have two videos on Gantt charts here - Quick Gantt chart: ua-cam.com/video/QZd3iXcueaI/v-deo.html and a more complete Gantt: ua-cam.com/video/KtR-CVxC5qA/v-deo.html
I love excel function
experienced teacher. thanks
You're very welcome Dawuda. Thank you for your support.
Can you explain how to apply the same on excel table (listobjects). Thanks
Thanks, my Teacher.
You're very welcome Abdul.
The video is very good.
thank you!
Thanks leila, very interesting :)
You're very welcome John :)
Thanks
You're very welcome.
very well done!
Can you please share link for many matches video?
Thank you for clearly explaining the function
Much appreciated
Subscribed. thanks
Hi Ms. Leila, Thanks for the Video. I found problem in using Find Function on number format. For example if find value is 15 and search range having number format and value is 15.000, then find function will have nothing.
Could you please look into this problem.
Love it Madamme, thank you
Hi Leila, thank you so much I have a question like 1030SE6 there are 2 values 104 and 105 in the data... if I want both of them in the same cell in one match found.. how do we do it.. thank u once again
Thank you, my Beloved & beautiful Excel Teacher.
You're very welcome Gopala. Thanks for dropping by :)
Hi leila,
great videos!
Im using this in order to find a certain cell, and once i find it i would like to activate the formula evaluator from the formula tab. is that possible?
thanks!
Thank you for the fine instructional video. Could you please post link to "Many Matches" lecture video?
I hope this will be helpful: ua-cam.com/video/fDB1Ktyhp3Y/v-deo.html
Dear Ms.Leila.thank u so much for this clip.it is realy informative.Actually i have my data i cell A1 in mutliple sheets and i want to search or find it on another sheet ( like your tutorial).I have no idea to how to do it.So,have u any clip or tutorial on it??
Dear Ms. Leila! Tks so much for your clip. It's clearly to understand. I practiced it well but I don't know how to get the result for: 1) number of values in column. 2) I want to get the address of found value in the last row but It always give 1 row in case the column have many same values. Pls hellp me to solve it. Many tks!
thks Leila :-)
You're very welcome Mario
Love u mam love so much
Mam I have a problems ,
I have data like
" 23a 50c 12k " in a one cell,
When I search 50c, the cells 50c should be bold and italic .
Please help me .
Regarding RANGE.FIND (same for interactive HOME - FIND or ctrl-F), did you know that searching 1200 by value, won't find cells whose value is 1200 (for example =1100+100, or =120*10) in case the format has a thousand separator (#,###)? What do you think about this?
Thanks for the lesson. This is no doubt a silly question but you say that your next video is on Finding Multiple Matches. Could you give me the link or the name of the video I can't find it?
Hi Leila, can you please show how to check a value for example from multiple text files using excel vba macro?
thanks.
anyone know how we can find multiuple vlaues? for example a value in A1 and B1. ?
Very good Leila, but it seems not to work when you are looking for a date, column C, in your example. I guess is a problem of conversion (strings??), can you clarify that?
How can I use cell reference as the Find value to another sheet so if the cell reference changes, it will find another cell based on that cell reference value. Thanks.
Hi Leila. Thanks so much for helping me learn VBA, your lessons have been a blessing. Am trying to find the second part of this video in the Udemy course but not able to locate it. Could you please help me find which section/lesson it is?
Found it if anyone is looking: Section 10, Lecture 79.
Anytime a particularly complex piece of code works, I always say "Thank God and Leila!" ;)
@@jasmeenmalhotra2225 where is exactly the video? care to share the link pls?
@@mdzen22e hey so Leila has this Udemy course on VBA. I had subscribed to it separately and the lecture number I mentioned corresponds to that. I highly recommend the course if you have some time to learn VBA from scratch. www.udemy.com/excel-vba-and-macros-course/
Thanks for the tutorial. Could you plz let us know which software you use to edit the videos?
You're welcome. I use Camtasia - you can see my full list of equipment here: www.xelplus.com/resources/#gear
Leila Gharani thank you
Hi Leila,
Can we use Find in 1st row instead of 1st column?
Great vid Leila, can this be done without using a button to run the Macro? I.e. A listen event for when the list selection changes? And can it select the column range for the match it finds?
Hi Stevon - Yes. You can run it directly from the specific event. You could also define a variable for the column...
Leila Gharani thanks for the reply, I used a "change" event on the sheet I wanted the find macro to run and used
"If Target.Address = "$A$1" Then Call (Macro name).. Now when I change the list selection it triggers the find macro. I also changed the compid.offset.value to compid.active which selects the "found" cell in the range.. Keep up the good work I've learned a lot from your vids x
Hi, what if I need is always a row (row 7), and not a colum? And how can I copy the range that's below the found item? My source file is a report that sometimes, depending on how I generate it, moves the ranges I need to copy from one column to another, but, as I said, the headers are always the same text and are always on row 7. I guess I should only have to adapt your explanation to rows? Thanks!
I'm trying to use an ActiveX Button to find a certain word in my spreadsheet. I have it where it goes to a certain row but when I add more rows, the Button still goes to the original button. I have 20 buttons in a large spreadsheet and each button starts a new category. I want to click that button so I don't have to scroll down hundreds of rows. Basically, I want to use a Command Button in place of CTRL F. Can you please help?
This is excellent and I own the course (as well as others that you produced). Within your "Unlock Excel VBA" course, which discussion number(s) cover this subject, especially searching a range for more than one value and pasting it? Thank you very much.
It's covered in section 10 - lecture: "Find method with Do Loop - Multiple Matches"
@@LeilaGharani thank you very much
How to handle the find error without defining the object. Can we do it with if function.
E.g I have two workbooks one has 5 column while other has 4 columns... So if I am finding the column header of ist workbook and I want to keep that as a general statement; however my 2nd workbook doesn't contain that column header; how should I avoid that situation/error .And how to skip further steps that follow in line with the find code.. If I am using find function for locating the column heads..
Here is the code that needs to be corrected
If cells.find(what:="Name", after:= active cell).activate
Selection.clearcontents
Active cell.value = "New name)
End if
please how can i find the properties for a button in excel 2016 as in the one similar to the properties in the developer tab but this time referring to a button instead
I'm receiving a type mismatch (Run-time error 13)? Any ideas?
hi there is any way that i can get the address value show in watches and put it in a variable?
Hello mam, my find function is not working in before double click event of worksheet. I checked correctly my code and there are no errors but when I run the code it gives me error and the range which I declared as range and used find function it gives me nothing instead of giving the cell address of the value which I want to find pls help me
I noticed, the 'What' parameter for the object Range was not expressed explicitly, (i.e. string or any excel type, 7.5 or "value to find") i receive an 'empty' for Set test_val = Cells.Find(what:="7.5", LookIn:=xlFormulas, lookat:=xlWhole)
, any suggestions?
How to write VBA code for find funcation
The value we want to find is in sheet 2 and from where we want to find is sheet 1
HELP does anyone knows how to do that when the referenced cell (in this case was B3) is in another sheet?
Its not just a case of referencing the other sheet, I need it to be dynamic, always using the cell F15 of the sheet the person is in
Leila, when looking for max value in row by Row.find have problem with matching small number, say 1.1E-11. Even .NumberFormat="" do not help (it solved issue when I had different number formatting in row cells) because general number format still show the number in scientific format. Any idea how I can fix the issue? Thanks
Hy Leila, I am getting the following error "Run time erro 9 - Subscript out of range". I have written exactly the same code as your example.
Would i be able to use this if I wanted to update the raw data? Example: if I wanted to FIND (AT1030) on your spreadsheet so that I can update the Customer Code and Article Code. How would I be able to change this with a push of a button? VS CTRL+F type the code in then TAB to the cells that I would like to update?
How can I include hidden rows? I mean: if there are hidden rows then the code would not get that hidden data, so how it could be fixed so the code don't ignore those hidden rows?
how can i search for a value in sheet 2 from a value i enter on sheet1, and then update the cells of that row with new information from sheet 1. Sheet 1 is a user form i created with vba that inputs new information one row at a time... but I cant' get sheet 1 to not put a duplicate, and instead update information of the value already there.
Hi Leila, I have been searching for an answer in many videos. Let me know if you can help with this. I have 2 large sets of data - Open Demand and supply. They have skill and grade in common. I want to match to see for eg. the no of matching associates available for open demands or vice versa. Will VBA help or advance excel formulas. Can you help with a detailed Video for it. Thanks, Vidhya.
Dear Ms Gharani,
First of all, I would like to thank you for a very good and educational video :-)
I also wonder where I can find the video about FindNext?
Regards
Peter, from Sweden
You're welcome Peter. Currently I have FIND NEXT just inside the course but I might put a video on UA-cam as well in the future :)
thx for your answer :-) It´s the Excel VBA & Macros-course, right?
What if you need to loop through many cells and find them on another column? It might find some, and others might not be found...
Can i use find to look a value inside an array declared inside vba code only?
Thanks in advance.
A fan of yours from India
You're welcome. There are other ways you can get that done - for example by using the filter function (for one dimensional array) or Instr function.
Actually i have a problem. In find function which i use ( "Set foundtaskname = aimsnapworksheet.Columns(1).Find(What:=searchtaskname, LookIn:=xlValues, LookAt:=xlWhole)") eventhough here searchtaskname is found, the foundtaskname still shows nothing. Do you know how to resolve this?
Can you please help me in this issue of runtime err 91 as the coding is
Set ParTAdreS1 = Sheet4.Cells.Range("C:C").Find(what:=Part1A, LookIn:=xlValues, lookat:=xlPart)
hi i have found list in column (A) of name this name is repeated several times, I am looking for a code that I fill cell in column (B) with first found if is empty is only once for second time I fill second empty cell these values by textbox
Now I'm try to do that but there are a problem faced me
What if the range of search in anther sheet pls quickly reply
Can someone tell the me the link for the 2nd part that deals with multiple instances? I couldn't find it anywhere :(
how to create a search box if dropdown in one sheet and data in another sheet.?
Does anyone knows how to create an Excel Macro that Searches Entire Workbook and Returns All Records which match a certain value from a drop down list? e.g. if we have a list of many large organization in a worksheet and have a list of all of their employees ( many per organization) in another worksheet. How we can see the records of all employees in an organization by selecting an individual organization from a list or drop down box, etc. I would appreciate if your insights.
hello ma'am i have 1 query ,suppose there is name containing 3 words Adi Roy Kapur ,so in that case how could we find the last name ("kapur") from it.please help me .
how to do a loop until it didn't find the selected text any more ?
Hello, can i ask the many matches button?
Hi, your are beautful
Hello, the video is interesting but I don't know if I can use it, maybe you can help me, I have a worksheet "Menu", at the top of cell "B2" I have a dropdown list with all 200 worksheets, in cell "B4 " do I have a push button, when I click on it I want to search in the worksheet of "B2" , the formula I used in Exce not in VBA:
=vlookup($A$1,indirect($B$2)2,False), and then I get what I want; but now how do i do this in VBA, can you help PLEASE.