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

22
3

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://wapchoi.net/

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

22 COMMENTS

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

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

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

  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!

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

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

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

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

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

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

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

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

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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here