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

Рет қаралды 192,351

Күн бұрын

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

Пікірлер: 168

@LeilaGharani 5 ай бұрын

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.

@excelisfun 6 жыл бұрын

Thanks for the VBA FIND Fun, Leila : )

@LeilaGharani 6 жыл бұрын

You're very welcome Mike :)

@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

@omkarvedpathak4937 3 жыл бұрын

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

@ckokse 6 жыл бұрын

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

@LeilaGharani 6 жыл бұрын

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

@warviksam 4 жыл бұрын

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

@without-u5965 6 жыл бұрын

Leila, thanks for FIND video :). Extremely useful

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

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

@Saurabh-ix5bx 4 жыл бұрын

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!

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

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

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

@turkaykoc8587 Жыл бұрын

Thank you so much for your good and lean explanations

@DominicPowell92 5 жыл бұрын

Excellent way of avoiding an error if nothing is found.

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

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

@carlosalfonso7504 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

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

@danevans8981 2 жыл бұрын

Very useful, thank you.

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

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

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

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

@UnlockSource 6 жыл бұрын

Nice Teaching

@dimassilva6822 3 жыл бұрын

Last but not least. Thank you. Great video.

@adamloepker1359 2 жыл бұрын

Thank you for helping clarify some syntax for me!

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

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

@fikilekentane6982 3 ай бұрын

Leila, you are awesome 💌

@rakeshkumarmadella7955 Ай бұрын

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

@suleymaninan5556 4 жыл бұрын

The video is very good.

@MyMrwrestling 5 жыл бұрын

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

@Pankaj-Verma- 5 жыл бұрын

I mean you are seriously great!

@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: kzfaq.info/get/bejne/h8CUZsyJyNvJkX0.html and a more complete Gantt: kzfaq.info/get/bejne/gdqCYKaH3amZoXU.html

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

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

@sagarnivgune475 4 жыл бұрын

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

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

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

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

@anassalane9567 4 жыл бұрын

THANK YOU A LOT ! 👍

@ExcelMadeEasy444 6 жыл бұрын

Good Explained ! Waiting for what next...

@LeilaGharani 6 жыл бұрын

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

@dawudayunusfarouk3098 6 жыл бұрын

experienced teacher. thanks

@LeilaGharani 6 жыл бұрын

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

@gmailid3934 6 жыл бұрын

Thanks

@LeilaGharani 6 жыл бұрын

You're very welcome.

@abdulraouf727 5 жыл бұрын

Thanks, my Teacher.

@LeilaGharani 5 жыл бұрын

You're very welcome Abdul.

@johnborg6005 6 жыл бұрын

Thanks leila, very interesting :)

@LeilaGharani 6 жыл бұрын

You're very welcome John :)

@xz7525 5 жыл бұрын

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

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

@omairbinenam6337 5 жыл бұрын

Subscribed. thanks

@stevennye5075 4 жыл бұрын

very well done!

@walek2001 6 жыл бұрын

thank you!

@damandeepsingh2859 3 жыл бұрын

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

@ForcaMessidona 2 жыл бұрын

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

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

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

@renzohisona1292 3 жыл бұрын

Love it Madamme, thank you

@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: kzfaq.info/get/bejne/nKpyZK6l3s7UY40.html

@Iwasns 6 жыл бұрын

I love excel function

@ryancollado2333 3 жыл бұрын

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

@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

@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

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

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

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

@gabrielfuchs8774 4 жыл бұрын

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

@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

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

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

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

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

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

@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

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

@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

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

@jackeyzhu3702 3 жыл бұрын

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

@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

@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

@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

@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

@myapp9067 2 жыл бұрын

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

@hosseinhosseinpoor4845 3 жыл бұрын

thanks.

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

@srivardhankandike4514 5 жыл бұрын

Love u mam love so much

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

@user-rida2002i 3 жыл бұрын

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

@Meolimo 6 жыл бұрын

thks Leila :-)

@LeilaGharani 6 жыл бұрын

You're very welcome Mario

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

@suriyapriya5535 5 ай бұрын

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?

@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

@punch3n3ergy37 4 жыл бұрын

how to do a loop until it didn't find the selected text any more ?

@nicerackz 4 жыл бұрын

can anyone tell me how to do a vba find that will select each highlighted cell (cell has no values), and will allow me to loop through all of them.

@icusawme2 4 жыл бұрын

Sub loop_and_DEBUGprint_value() 'Application.EnableEvents = False 'turn off screen updating ' You will have to manually select a range or select with a program Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Dim rng As Range Set rng = Selection Dim cel As Range For Each cel In rng.Cells With cel 'Do something here If Not cel.Interior.Color = 16777215 Then Debug.Print .Address & ":" & .Value End If End With Next cel Application.EnableEvents = True ' Turn on screen updating End Sub

@rimantasdanilevicius6754 Жыл бұрын

Maybe someone knows how to loop though column A cells and compare searched value with each value in column B and if such value does not exist in column B in that cell then format that cell yellow?

@HybridFox_Ava 2 жыл бұрын

Hello, can i ask the many matches button?

@1gopalakrishnarao 6 жыл бұрын

Thank you, my Beloved & beautiful Excel Teacher.

@LeilaGharani 6 жыл бұрын

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

@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 KZfaq as well in the future :)

@plelkes 5 жыл бұрын

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