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

[@LeilaGharani]

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

[@andrewwolfe3836]

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

[@internet_wanderer8316]

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

[@surjagain]

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]

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]

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]

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

[@oscarrv50]

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]

Thanks for the VBA FIND Fun, Leila : )

[@LeilaGharani]

You're very welcome Mike :)

[@DominicPowell92]

Excellent way of avoiding an error if nothing is found.

[@siddarajm141177]

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]

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

[@monicakocheta5502]

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]

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]

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

[@turkaykoc8587]

Thank you so much for your good and lean explanations

[@krn14242]

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

[@LeilaGharani]

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

[@dimassilva6822]

Last but not least. Thank you. Great video.

[@fikilekentane6982]

Leila, you are awesome 💌

[@michaelchoate6550]

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

[@LeilaGharani]

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

[@adamloepker1359]

Thank you for helping clarify some syntax for me!

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

[@ismailismaili0071]

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]

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

[@ashrafgharsan553]

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

[@LeilaGharani]

You're welcome!

[@warviksam]

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

[@ckokse]

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

[@LeilaGharani]

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

[@danevans8981]

Very useful, thank you.

[@naadiyahjohnson5585]

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

[@LeilaGharani]

Thank you Naadiyah :) Glad to see you here.

[@carlosalfonso7504]

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

[@LeilaGharani]

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

[@Saurabh-ix5bx]

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!

[@ForcaMessidona]

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

[@vietthangag]

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!

[@nimszi]

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]

The video is very good.

[@MalinaC]

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

[@LeilaGharani]

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

[@asimali9046]

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

[@ExcelMadeEasy444]

Good Explained ! Waiting for what next...

[@LeilaGharani]

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

[@1gopalakrishnarao]

Thank you, my Beloved & beautiful Excel Teacher.

[@LeilaGharani]

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

[@clickclick8761]

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

[@LeilaGharani]

I have two videos on Gantt charts here - Quick Gantt chart: kzbin.info/www/bejne/h4vHZJyOmNqYl6s and a more complete Gantt: kzbin.info/www/bejne/gaW1XnaMrahop6M

[@maddymaddy3323]

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 .

[@vidhyasubramaniam6987]

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.

[@JI-tz1gz]

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?

[@without-u5965]

Leila, thanks for FIND video :). Extremely useful

[@eduarchavarria]

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!

[@sagarnivgune475]

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

[@ryancollado2333]

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

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

[@Pankaj-Verma-]

I mean you are seriously great!

[@markgodwin6003]

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"

[@אליהרו]

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!

[@anassalane9567]

THANK YOU A LOT ! 👍

[@MyMrwrestling]

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

[@danniellehurley9647]

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?

[@renzohisona1292]

Love it Madamme, thank you

[@salrangel7049]

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?

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

[@dawudayunusfarouk3098]

experienced teacher. thanks

[@LeilaGharani]

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

[@johnborg6005]

Thanks leila, very interesting :)

[@LeilaGharani]

You're very welcome John :)

[@damandeepsingh2859]

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

[@irlnavarro]

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.

[@abdulraouf727]

Thanks, my Teacher.

[@LeilaGharani]

You're very welcome Abdul.

[@soonerbrad44]

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?

[@siddarajm141177]

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)

[@this446]

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?

[@UnlockSource]

Nice Teaching

[@santywsu]

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

[@LeilaGharani]

I hope this will be helpful: kzbin.info/www/bejne/nHWlYn6qrs2jabs

[@stefanogattoCH]

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?

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

[@jackeyzhu3702]

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

[@yashlimbachiya7327]

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

[@joshuaobengagyemang6264]

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

[@dataworld8683]

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 .

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

[@pankajsharma7233]

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 .

[@stevonduddly5709]

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]

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

[@stevonduddly5709]

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

[@mikelennon1078]

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.

[@gerardvaneggermond2067]

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.

[@tannertucker22]

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]

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

[@tannertucker22]

@@LeilaGharani thank you very much

[@FrancescoGrosso1094]

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

[@EternalSpartan]

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.

[@gabrielfuchs8774]

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

[@suriyapriya5535]

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?

[@jasmeenmalhotra2225]

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]

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]

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

[@jasmeenmalhotra2225]

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

[@stevennye5075]

very well done!

[@xz7525]

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

[@EnayatShaikh]

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

[@LeilaGharani]

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

[@EnayatShaikh]

Leila Gharani thank you

[@zozozozo43]

♥♥ Idea1: If we want match with last data in SomeRange1 we use the Parameter: SearchDirection:=xlPrevious Set vRangeFindLast = SomeRange1.Find(what:="Something", After:=SomeRange2, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True) 'Last record p.s: The first match is with Parameter: SearchDirection:=xlNext Idea2: If we want replace something1 with something2 in SomeRange, we use Do_Loop_While and Key GoTo as follow Dim vRangeFindName As Range Dim vStringAddress As String ' Active some sheet ♥ With Range("a3:d9") ' For example :) Set vRangeFindName = .Find(what:="something1 ", LookIn:=xlValues, lookat:=xlWhole, _ searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True) If Not vRangeFindName Is Nothing Then vStringAddress = vRangeFindName.Address ' The wisdom =) Do DoEvents vRangeFindName = "something2" Set vRangeFindName = .FindNext(vRangeFindName) If vRangeFindName Is Nothing Then GoTo AmDone End If Loop While vRangeFindName.Address vStringAddress End If AmDone: Dim vIntQuestion As Integer vIntQuestion = InputBox("Enter some Numer", "A crucial question") If vIntQuestion = 971 Then MsgBox "May Allah Keep Leila", , "A Prayer for her" End With ♥♥

[@LeilaGharani]

Thank you so much Assem for the codes! Also for the hidden message! Really appreciate your support.

[@zozozozo43]

I can now say am glad and all stop, exit sub, wbLearn.Close and Application.Quit can't stop my smile "infinite loop"

[@TheUnknownU]

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

[@myapp9067]

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

[@dimassilva6822]

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

[@HybridFox_Ava]

Hello, can i ask the many matches button?

[@mairaneves4959]

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

[@omairbinenam6337]

Subscribed. thanks

[@user-rida2002i]

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

[@plelkes]

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]

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

[@plelkes]

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

[@Iwasns]

I love excel function

[@AniManuSCh]

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?

[@deepaksoni2007]

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]

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.

[@jean-francoistremblay4403]

Hi Leila, I have to do the same thing through different sheets and it is not working. What am I missing? here is my code : Sub ValeurDansTableauPutEnFonctionDuPrixDeLevee() Dim PrixDeLevee As Range Set PrixDeLevee = Sheets(6).Range("A:A").Find(What:=Sheets(1).Range("D31").Value, LookIn:=xlValues, lookat:=xlWhole) End Sub I have no values. Thanks, JF

[@mallukatabu6193]

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]

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

[@punch3n3ergy37]

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

[@enricopunta2819]

Hello, how to find a specific formula?

[@srivardhankandike4514]

Love u mam love so much