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

Подписаться 2,7 млн

Просмотров 193 тыс.

50% 1

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

Опубликовано:

8 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 168

@LeilaGharani 6 месяцев назад

Get access to the complete Excel VBA course here 👉 www.xelplus.com/course/excel-vba-excel-macros/

@andrewwolfe3836 4 года назад

"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.

@oscarrv50 5 лет назад

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.

@surjagain 5 лет назад

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.

@LeilaGharani 5 лет назад

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.

@nancyd.5990 5 лет назад

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 😀

@LeilaGharani 5 лет назад

Thank you so much Nanzyz! I'm very glad you find the tutorials easy to follow :)

@siddarajm141177 3 года назад

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

@internet_wanderer8316 3 года назад

I lost interest in learning vba and excel functions but thanks to your channel here, it makes it interesting and motivating. Thanks a lot.

@omkarvedpathak4937 4 года назад

Really great tutorial, also very soothing voice. Thank you so much!!

@DominicPowell92 5 лет назад

Excellent way of avoiding an error if nothing is found.

@excelisfun 6 лет назад

Thanks for the VBA FIND Fun, Leila : )

@LeilaGharani 6 лет назад

You're very welcome Mike :)

@warviksam 4 года назад

Exactly the kind of macro I was looking for. Thank you very much.

@monicakocheta5502 4 года назад

Hi Leila, I'm new to Macros but this video, was so made so easy that I was able to make my code work.

@jamesjujare2599 6 лет назад

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...!!!

@LeilaGharani 6 лет назад

;-) 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.

@krn14242 6 лет назад

Thanks, great explanation of the IF NOT, IS NOTHING syntax. Makes sense to me now.

@LeilaGharani 6 лет назад

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 :)

@ismailismaili0071 6 лет назад

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.

@LeilaGharani 6 лет назад

Thank you Ismail. I'm glad you find it helpful.

@ckokse 6 лет назад

I love the VBA videos, so helpful. Thank you!

@LeilaGharani 6 лет назад

You're welcome - I remember you asking for them :)

@naadiyahjohnson5585 6 лет назад

I was 1st like this time round. All ur vids are so helpful and easy to understand

@LeilaGharani 6 лет назад

Thank you Naadiyah :) Glad to see you here.

@without-u5965 6 лет назад

Leila, thanks for FIND video :). Extremely useful

@ashrafgharsan553 3 года назад

Awesome tutorial! I could avoid a nested for loop reducing runtime greatly thanks to your tutorial. Thank you!

@LeilaGharani 3 года назад

You're welcome!

@Saurabh-ix5bx 5 лет назад

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!

@turkaykoc8587 Год назад

Thank you so much for your good and lean explanations

@michaelchoate6550 5 лет назад

Elegant solutions with pleasant, informative explanations. What more could we ask for? Thanks.

@LeilaGharani 5 лет назад

You're very welcome Michael. Glad you like it.

@carlosalfonso7504 5 лет назад

Very useful VBA, and very well explained, thank you so much!

@LeilaGharani 5 лет назад

You're very welcome Carlos. Glad you find the video useful.

@MalinaC 6 лет назад

Leila, thanks for FIND video :). Extremly useful :)

@LeilaGharani 6 лет назад

Thank you Malina :) Fully agree - I use it in 90% of my VBA files.

@dimassilva6822 3 года назад

Last but not least. Thank you. Great video.

@maddymaddy3323 4 года назад

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 .

@fikilekentane6982 3 месяца назад

Leila, you are awesome 💌

@adamloepker1359 2 года назад

Thank you for helping clarify some syntax for me!

@danevans8981 2 года назад

Very useful, thank you.

@UnlockSource 6 лет назад

Nice Teaching

@iahmedx 6 лет назад

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.

@suleymaninan5556 4 года назад

The video is very good.

@markgodwin6003 3 года назад

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"

@clickclick8761 6 лет назад

A professional video tutorial, thank you! Do you have a similar for Gantt Project in Excel?

@LeilaGharani 6 лет назад

I have two videos on Gantt charts here - Quick Gantt chart: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QZd3iXcueaI.html and a more complete Gantt: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-KtR-CVxC5qA.html

@gmailid3934 6 лет назад

Thanks

@LeilaGharani 6 лет назад

You're very welcome.

@ExcelMadeEasy444 6 лет назад

Good Explained ! Waiting for what next...

@LeilaGharani 6 лет назад

Glad you like it.... VBA is coming again soon...

@Pankaj-Verma- 5 лет назад

I mean you are seriously great!

@sagarnivgune475 4 года назад

Can you please share link for many matches video? Thank you for clearly explaining the function Much appreciated

@anassalane9567 4 года назад

THANK YOU A LOT ! 👍

@dawudayunusfarouk3098 6 лет назад

experienced teacher. thanks

@LeilaGharani 6 лет назад

You're very welcome Dawuda. Thank you for your support.

@rakeshkumarmadella7955 2 месяца назад

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

@santywsu 5 лет назад

Thank you for the fine instructional video. Could you please post link to "Many Matches" lecture video?

@LeilaGharani 5 лет назад

I hope this will be helpful: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-fDB1Ktyhp3Y.html

@johnborg6005 6 лет назад

Thanks leila, very interesting :)

@LeilaGharani 6 лет назад

You're very welcome John :)

@stevennye5075 4 года назад

very well done!

@Iwasns 6 лет назад

I love excel function

@dataworld8683 4 года назад

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 .

@abdulraouf727 5 лет назад

Thanks, my Teacher.

@LeilaGharani 5 лет назад

You're very welcome Abdul.

@user-ov5bi1ht9r 3 года назад

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!

@renzohisona1292 3 года назад

Love it Madamme, thank you

@vietthangag 3 года назад

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!

@walek2001 6 лет назад

thank you!

@omairbinenam6337 5 лет назад

Subscribed. thanks

@danniellehurley9647 4 года назад

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?

@asimali9046 3 года назад

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??

@MyMrwrestling 5 лет назад

anyone know how we can find multiuple vlaues? for example a value in A1 and B1. ?

@stefanogattoCH 5 лет назад

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?

@ForcaMessidona 2 года назад

Can you explain how to apply the same on excel table (listobjects). Thanks

@JI-tz1gz 4 года назад

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?

@1gopalakrishnarao 6 лет назад

Thank you, my Beloved & beautiful Excel Teacher.

@LeilaGharani 6 лет назад

You're very welcome Gopala. Thanks for dropping by :)

@irlnavarro 4 года назад

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.

@xz7525 5 лет назад

I'm receiving a type mismatch (Run-time error 13)? Any ideas?

@ryancollado2333 3 года назад

Hi Leila, can you please show how to check a value for example from multiple text files using excel vba macro?

@jasmeenmalhotra2225 5 лет назад

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?

@jasmeenmalhotra2225 5 лет назад

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!" ;)

@mdzen22e 5 лет назад

@@jasmeenmalhotra2225 where is exactly the video? care to share the link pls?

@jasmeenmalhotra2225 5 лет назад

@@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/

@srivardhankandike4514 5 лет назад

Love u mam love so much

@hosseinhosseinpoor4845 3 года назад

thanks.

@soonerbrad44 5 лет назад

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?

@eduarchavarria 4 года назад

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!

@stevonduddly5709 6 лет назад

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?

@LeilaGharani 6 лет назад

Hi Stevon - Yes. You can run it directly from the specific event. You could also define a variable for the column...

@stevonduddly5709 6 лет назад

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

@MrJBenvenuto Год назад

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.

@yashlimbachiya7327 3 года назад

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

@damandeepsingh2859 3 года назад

Hi Leila, Can we use Find in 1st row instead of 1st column?

@vidhyasubramaniam6987 2 года назад

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.

@joshuaobengagyemang6264 5 лет назад

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

@salrangel7049 4 года назад

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?

@AniManuSCh 4 года назад

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?

@gabrielfuchs8774 4 года назад

hi there is any way that i can get the address value show in watches and put it in a variable?

@tannertucker22 4 года назад

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.

@LeilaGharani 4 года назад

It's covered in section 10 - lecture: "Find method with Do Loop - Multiple Matches"

@tannertucker22 4 года назад

@@LeilaGharani thank you very much

@EnayatShaikh 6 лет назад

Thanks for the tutorial. Could you plz let us know which software you use to edit the videos?

@LeilaGharani 6 лет назад

You're welcome. I use Camtasia - you can see my full list of equipment here: www.xelplus.com/resources/#gear

@EnayatShaikh 6 лет назад

Leila Gharani thank you

@mairaneves4959 3 года назад

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

@deepaksoni2007 6 лет назад

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

@LeilaGharani 6 лет назад

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.

@this446 5 лет назад

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?

@TheUnknownU 5 лет назад

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

@EternalSpartan 4 года назад

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.

@jackeyzhu3702 3 года назад

Can someone tell the me the link for the 2nd part that deals with multiple instances? I couldn't find it anywhere :(

@plelkes 5 лет назад

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

@LeilaGharani 5 лет назад

You're welcome Peter. Currently I have FIND NEXT just inside the course but I might put a video on RU-vid as well in the future :)

@plelkes 5 лет назад

thx for your answer :-) It´s the Excel VBA & Macros-course, right?

@naimsaid4451 Год назад

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

@mallukatabu6193 6 лет назад

dera leila if we using wms (warehouse)in excel, how to create in there excel for example ,,,,,ean,qty,location ok ! what we need cods in vba pls replay me

@LeilaGharani 6 лет назад

I'm not sure about your question Mallu....

@mikelennon1078 3 года назад

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.

@siddarajm141177 3 года назад

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)

@myapp9067 3 года назад

how to create a search box if dropdown in one sheet and data in another sheet.?

@Meolimo 6 лет назад

thks Leila :-)

@LeilaGharani 6 лет назад

You're very welcome Mario

@Kris-82 Год назад

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

@FrancescoGrosso1094 2 года назад

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...

@suriyapriya5535 6 месяцев назад

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?

@HybridFox_Ava 2 года назад

Hello, can i ask the many matches button?

@mohdsubhan9887 Год назад

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

@user-rida2002i 4 года назад

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

@pankajsharma7233 4 года назад

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 .

@gerardvaneggermond2067 11 месяцев назад

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.

@dimassilva6822 3 года назад

Why dou you use "If not compid=nothing then a else b" intead of "If compid=nothing then b else a"?