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



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:

★ My Online Excel Courses ★

Courses ►

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

EXCEL RESOURCES I Recommend:

Get Office 365:
Microsoft Surface:

GEAR
Camera:
Screen recorder:
Microphone:
Lights:

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

Nguồn: https://cardtutorialonly.com/

Xem thêm bài viết khác: https://cardtutorialonly.com/tong-hop/

All Comments

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

    Manu S Ch June 10, 2020 12:45 pm Reply
  • 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 .

    Pankaj Sharma June 10, 2020 12:45 pm Reply
  • 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 .

    maddy maddy June 10, 2020 12:45 pm Reply
  • 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!

    Eduardo Chavarría June 10, 2020 12:45 pm Reply
  • 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?

    Sal Rangel June 10, 2020 12:45 pm Reply
  • The video is very good.

    süleyman Inan June 10, 2020 12:45 pm Reply
  • Please anyone resolve my problem

    Rohit Prasad June 10, 2020 12:45 pm Reply
  • C D E F G H I J K L
    AdmNo Eng Hin Mat Skt Sci Sst Gk Ms Comp
    ===== === === === === == === === == ====
    32/2018 Yes Yes Yes Yes Yes Yes Yes Yes Yes

    252/2016 Yes Yes No Yes Yes Yes Yes Yes Yes

    082/2014 Yes Yes No No Yes Yes Yes Yes Yes

    110/2014 Yes Yes No Yes Yes Yes Yes Yes Yes

    119/2015 Yes Yes No No Yes Yes Yes Yes Yes

    121/2015 Yes Yes No No Yes Yes Yes Yes Yes

    084/2014 Yes Yes No Yes Yes Yes Yes Yes Yes

    147/2015 Yes Yes No Yes Yes Yes Yes Yes Yes

    122/2015 Yes Yes No No Yes Yes Yes Yes Yes

    166/2016 Yes Yes No Yes Yes Yes Yes Yes Yes

    36/2018 Yes Yes No No Yes Yes Yes Yes Yes

    270/2017 Yes Yes No No Yes Yes Yes Yes Yes

    108/2014 Yes Yes No Yes Yes Yes Yes Yes Yes

    236/2016 Yes Yes No Yes Yes Yes Yes Yes Yes

    111/2014 Yes Yes No Yes Yes Yes Yes Yes Yes

    109/2014 Yes Yes No Yes Yes Yes Yes Yes Yes

    284/2017 Yes Yes No Yes Yes Yes Yes Yes Yes

    078/2014 Yes Yes No Yes Yes Yes Yes Yes Yes

    113/2014 Yes Yes No Yes Yes Yes Yes Yes Yes

    143/2015 Yes Yes No Yes Yes Yes Yes Yes Yes

    092/2014 Yes Yes No No Yes Yes Yes Yes Yes

    This is my simple excel file how to find the respective AdmNo when user changes the Yes or No from Data Validation DropDown

    Rohit Prasad June 10, 2020 12:45 pm Reply
  • Exactly the kind of macro I was looking for. Thank you very much.

    Vikrant Samuel June 10, 2020 12:45 pm Reply
  • 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?

    Dannielle Hurley June 10, 2020 12:45 pm Reply
  • 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?

    JI June 10, 2020 12:45 pm Reply
  • hi, I love all your excel vba's, may I ask something? Q: how to AUTO pop-up the find application (the one appears when we do CTRL+F) when I activate Sheet1? I mean, if I clicked "Sheet1" there is already a ("Ctrl+F") find app ready to input a certain values or text on it. (This is my situation, I have different similar data that I want to find everytime I clicked a certain sheet on column A or the very first column. so i want to save like few seconds on everytime I look a data on it.) I find it more convenient to me to use the excel CTRL+F app. Thank you more power!

    abill smith June 10, 2020 12:45 pm Reply
  • 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 .

    Nibedan Bhattacharjee June 10, 2020 12:45 pm Reply
  • hi there is any way that i can get the address value show in watches and put it in a variable?

    Gabriel Fuchs June 10, 2020 12:45 pm Reply
  • THANK YOU A LOT ! 👍

    Anass Alane June 10, 2020 12:45 pm Reply
  • "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.

    Andrew Wolfe June 10, 2020 12:45 pm Reply
  • 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.

    Ion Navarro June 10, 2020 12:45 pm Reply
  • Can you please share link for many matches video?
    Thank you for clearly explaining the function
    Much appreciated

    Sagar Nivgune June 10, 2020 12:45 pm Reply
  • 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.

    Eternal Spartan June 10, 2020 12:45 pm Reply
  • I need code that finds the largest value that I can +1 to and use it as a NextVal – – anyone?

    Robert Long June 10, 2020 12:45 pm Reply
  • 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.

    nicerackz June 10, 2020 12:45 pm Reply
  • how to do a loop until it didn't find the selected text any more ?

    Punch 3n3ergy June 10, 2020 12:45 pm Reply
  • very well done!

    Steven Nye June 10, 2020 12:45 pm Reply
  • Hi, your are beautful

    optimiste optimiste June 10, 2020 12:45 pm Reply
  • Dear Madam I need excel text function by VBA code

    I mean WorksheetFunction.text(12,"00000")
    Desire result =00012

    How it possible by VBA

    KUMAR SURAJ June 10, 2020 12:45 pm Reply
  • 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!

    Saurabh June 10, 2020 12:45 pm Reply
  • Very useful VBA, and very well explained, thank you so much!

    Carlos Alfonso June 10, 2020 12:45 pm Reply
  • 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

    Joshua Obeng.A June 10, 2020 12:45 pm Reply
  • I'm receiving a type mismatch (Run-time error 13)? Any ideas?

    Doug Newton June 10, 2020 12:45 pm Reply
  • Elegant solutions with pleasant, informative explanations. What more could we ask for? Thanks.

    Michael Choate June 10, 2020 12:45 pm Reply
  • anyone know how we can find multiuple vlaues? for example a value in A1 and B1. ?

    Daniel 101 June 10, 2020 12:45 pm Reply
  • 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

    uzair malik June 10, 2020 12:45 pm Reply
  • Thanks, my Teacher.

    Abdul Raouf June 10, 2020 12:45 pm Reply
  • Hi, could you do a video for finding dates.

    Ife Nketa June 10, 2020 12:45 pm Reply
  • 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?

    this446 June 10, 2020 12:45 pm Reply
  • I mean you are seriously great!

    Pankaj Verma June 10, 2020 12:45 pm Reply
  • Thank you for the fine instructional video. Could you please post link to "Many Matches" lecture video?

    Santosh Nareddy June 10, 2020 12:45 pm Reply
  • 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?

    Stefano Gatto June 10, 2020 12:45 pm Reply
  • Is vba worth learning I’ve heard that is dying in the industry

    Jayjay F June 10, 2020 12:45 pm Reply
  • 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.

    OSCAR VERA June 10, 2020 12:45 pm Reply
  • 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?

    Brad Naifeh June 10, 2020 12:45 pm Reply
  • 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 😀

    Nancy D. June 10, 2020 12:45 pm Reply
  • Excellent way of avoiding an error if nothing is found.

    Dom June 10, 2020 12:45 pm Reply
  • Subscribed. thanks

    Omair Enam June 10, 2020 12:45 pm Reply
  • 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?

    Jasmeen Malhotra June 10, 2020 12:45 pm Reply
  • Love u mam love so much

    srivardhan kandike June 10, 2020 12:45 pm Reply
  • Hi Leila, great video and thank you so much. Do you also have the sample file or video for the Many Matches as that is exactly I was looking or? Thanks in advance.

    Etem Kaya June 10, 2020 12:45 pm Reply

Leave a Reply

Your email address will not be published. Required fields are marked *