Excel VBA Introduction Part 15a - Find and FindNext

[@maurocastagnera8949]

Dear Andrew, I would stay hours and hours and hours watching your videos without ever getting tired! You are really the best teacher on the web! 👏👏👏

[@MyAudioBookCompilation]

This is the very first video that motivated me to learn more about vba. It was 5 years ago.

[@aksekant]

God Bless You. You are a genius, been trying to follow your VBA tutorials for more than a year. I just could not locate such preciousness anywhere on net with which you organize your learning and create such great videos. Bless you

[@MyAudioBookCompilation]

Greetings everyone specially to Mr. Andrew Gould. This is the very first video that I have watched in this channel few years ago and until now for me this channel is the best! From the Philippines.. Thank you so much sir.

[@kai-yg1xp]

What is simply amazing about your videos is that somewhere in the middle I make a pause, ask myself a question, then I continue watching and... see the answer :-). Learning with you is a real pleasure. Many thanks!

[@maurocastagnera8949]

Great Andrew! I do not know how much time I will use to watch all your videos about the VBA, but certainly it is one of the goals I have given in my life! Not only to learn something really important and interesting for my work and personal culture too, but also because of the immense work you've done on this subject you deserve to be heard up to the last second of the last video! I am watching this video and writing this comment at 6.30 a.m. on Sunday morning and this can tell you how much interest and admiration I have for your work! With deep appreciation and gratitude. Greetings from Italy.

[@scotolivera8207]

Couldn't Agree More, Same Here

[@RS-zo8yi]

Yes agreed...the best VBA teaching...simple way...i didnt find it easier to learn than before...thank you so much WiseOwl....

[@qasimawan3568]

This is a great introduction to using the "Find" & FindNext methods in VBA. Hopefully, I will start using this in my projects! Thanks Andy for the great content!

[@WiseOwlTutorials]

You're welcome Qasim, thanks for watching!

[@lokironin6934]

Had zero experience with VBA - started with your first video and now I am here! I feel already a bit confident and I want to thank you for your excellent work!! I am addicted and it turns out to be a lot of fun!

[@AutomationDose4U]

You know because of you and with help of your lectures I got job opportunity you are the best!!!!! Thanks for such generic lectures, its request is it possible to make videos on Access VBA. Your lectures are still reference for me whenever I stuck somewhere.

[@WiseOwlTutorials]

Congratulations on the new job! It would be great to have time to add Access VBA videos but I'm afraid that it's unlikely to happen in the near future.

[@tfcscrewdriver]

Watched 16 videos in sequence so far, all videos are so very well done. Thanks so much again!

[@nunoponts7951]

thank you !! by the way !I'm beginner to VBA programming.I watched many vba videos ,but when I began to follow your vba series I really made a great progress ! your way of teaching is just awesome ! !

[@ghajeev]

Your videos are just out of this world! You're simply amazing!!!!

[@tensor131]

I managed to follow all of this, and my knowledge of VBA is pretty meagre, so ipso facto this is a brilliant video!! Thank you .. I now intend to go back and watch all your earlier videos so I can understand VBA properly!!

[@ankitsalvi9788]

Your videos are ready useful for all VBA beginners. Well done. Great job.

[@tayyabatfarms]

Lovely, explained amazingly in very easily understandable manner. Thanks a lot

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@edoardoanson3215]

Thank you for your tutorial! I work in Real Estate and It's teaching me how to apply simple code to organize large data.

[@zyambo]

You're simply the best! I hope you get rewarded for your awesome work.

[@suttonmatthew1142]

i know I'm pretty off topic but do anyone know a good site to stream newly released movies online?

[@eddieroger4397]

@Sutton Matthew i watch on FlixZone. Just google for it =)

[@martinjackson558]

@Eddie Roger Definitely, been watching on Flixzone for since april myself :)

[@suttonmatthew1142]

@Eddie Roger Thanks, I signed up and it seems to work :) I really appreciate it !!

[@eddieroger4397]

@Sutton Matthew You are welcome xD

[@scotolivera8207]

I've Downloaded All The playlist 77 Videos Just In Case. Simply You Are The Best. thanks a lot

[@guthrie_the_wizard]

Tremendous work. I recommend this series to my team all the time. Really appreciate the videos!

[@majidjamili2157]

That was amazing! you've saved a company by this simple tutorial

[@nikunjbhatt2124]

You are such an awesome teacher. I am doing a thesis project on tool development in 3D Software called CATIA. It is also with Macro Programming. Some basic things are different from Excel but still these videos are helping me a lot in developing my tool. Thank you very much Andrew for your efforts.

[@portapere]

Wow Andrew, you've done a great job again! We really missed your tutorials on vba this last months. Thanks a lot!

[@wooh88]

Thanks!. The best Tutorial Series of VBA I ever seen!

[@CaptClaudo]

was stuck on how using findnext, this video has helped me immensely. looking forward in viewing your other videos.

[@njvanheerden2157]

Really excellent presentation. Your VBA knowledge is awesome!

[@michaelhenderson5098]

Thanks for posting this video. I used some of the code to make it easy for me to search and select a cell in a large list of data so I can edit the data in the cell. Works good.

[@zubairkahn1983]

Hi Andrew, I wrote a very simple code which should go to different sheets in a workbook and replace if it finds a particular string. Its not moving to next sheet tab neither telling me the error message. Since its not moving to next sheet, so its not changing the value as well. Only for the sheet which is selected, it does change what is asked for. Thanks. Here is the code: Sub ChangeName() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Cells(1, 2).Replace What:="GH", Replacement:="PH" Next ws End Sub

[@WiseOwlTutorials]

Hi Zubair! The For Each loop doesn't select each worksheet unless you include the code to do so: For Each ws in ActiveWorkbook.Worksheets ws.Select You can achieve the results you want without selecting the worksheets by qualifying your reference to the Cells property ws.Cells(1, 2).Replace etc. I hope it helps!

[@reeseagarcia]

Applying the Find Method to a Range: 1:01 Making the Find Method Case Sensitive: 3:56 Finding Whole Words or Parts of Words: 5:49 Dealing With Finding Nothing: 8:35 Creating a Basic Search System: 12:17 Using the FindNext Method: 17:00 Apply Conditions to Loops: 20:10

[@janezklun]

Found Find and FindNext, great one, thanks

[@WiseOwlTutorials]

Glad you liked it Janez!

[@balareddy7563]

Sir you are the best, thank you providing all this. what you are doing is awesome.

[@ceoleo]

Thank you very much Andrew. This tutorial was extremely well done!

[@tajsay]

that was a another great video indeed. thanks Andrew

[@WiseOwlTutorials]

Thank you for watching, Sayyad!

[@ynagae]

Thank you so much for the detailed and very easy to understand video!

[@Neyolight]

Thanks for this video. Its on point and explains the concept very well.

[@fazalwahab11]

Wow! This is great tutorial what I was looking for. Thank you so much

[@nadermounir8228]

Amazing. Thank you Andrew 😊

[@SatishDubeyG]

You are the one who made my career..Thanks and big thanks...keep this work up...I really appreciate if you can do some video on Excel VBA and Sharepoint integration.

[@SatishDubeyG]

NP....Still you are the best...

[@secunatutoriais2537]

You are great. Owesome video. Thanks!

[@erickbatista2732]

Thanks, very simple and helpful.

[@PADRONJORGEL]

Awesome, the explanation simply clear as a Diamond, thanks again. Suggestion , could you give a link where the data you are using for the excercises could be dowloaded ?, so people can practice with it later on watching the videos one more time. Myself already typed the data on a sheet and use it on a few excercise

[@francist3979]

Fantastic stuff!

[@prernaarora6928]

Can we use the countif function if we have text that is a part of the cell content . For example i have to find the number no "eds" in a range of cells. And the cell values may not necessarily start with eds or end with eds but eds would be a part of the cell value.

[@iamsopure1043]

An epic tutorial. by the way, find function in excel vba is quite weird especially when trying to find a value in number format.

[@ryan21370]

Very clear and great video!

[@StationOnLife]

This is such a wonderful, very helpful tutorial which I have been looking for. I have a request. Would you do another tutorial as a continuing of this tutorial, to show how to write VBA for a task that, once the "film cell" found, the task is "TO GO TO THAT CELL? Thank you.. (The problem I have is there is 1000 film, and the film I was looking for was at the B900, and when the message box giving the message but did not go to that B900 cell, which kinda didn't do me much help because I still have to search for that B900 cell manually.). Thank you for doing this tutorial. And thank you for any further help on this request. Thanks again

[@tannertucker22]

Your videos are excellent. Do you know of a way to loop through an entire range of cells and compare the contents of each cell in that range to the contents of another range of cells. If a match is found, then do something (e.g. copy matched cell context to another cell)? Thank you.

[@ManyStandAlone]

Pete, your voice is so relaxing and soothing that its dangerous for me to watch your vids in bed. They are awesome vids though.

[@mkaraakaya]

Awesome and amazing! Helped me a ton

[@MedoMedo-op3em]

very informative, Thank you

[@vishalsharma7058]

Hi Sir, I have to two workbooks wb1 and wb2 now I want to copy the data from sheet1 of wb1 by searching the particular column by its "name" and then pasting in sheet1 of wb2 by again finding that particular column by its "name" then pasting the wb1 sheet1 data in sheet1 of wb2. I have multiple columns in the sheet1 of wb1. I want to loop through all columns that I want in sheet1 of wb1 and repeat above task for them. And While finding column it should find by column "name" and not by column number, since my sheets column position keeps changing. Please help on this....!!

[@itsdaj]

Wow. That is all I can say. Thanks for making that look easy and simplifying it. I have a question that has haunted me for years with Excel and I am wondering if you would do a video about it. This video has gave me great ideas and I used some of this to solve the problem. Actually I have solutions but they are clumsy and do not take advantage of items like 'find' for ranges. The problem is this: I have two worksheets and I need to compare column A in one a column in the second (let's say column A to keep it simple), when a match is found I need to copy any number of cells in the row where the match was found. So if A5 has a match in sheet 1 with A3 in Sheet 2 then I want to copy B5 and E5 to sheet2 B5 and E5. Maybe the columns will not match up exactly with the source. It is a basic compare, find a match, then copy data from one sheet to the same row of the matched row in the other sheet. I can share what I did if you like.

[@itsdaj]

Yes vlookup can work but when there are 10 people repeating the same tasks for about 200 spreadsheets, executing VBA would be faster. Plus it removes the possibility of human error and standardizes the work.

[@Search4Knowledge]

Great video. I just find it odd that your smart intelli list pops up more often than mine. Like when you typed: Set filmcell = searchrange.findnext Mine could not generate findnext at this point.

[@iamsmtutube]

Excellent...Great...!

[@matthoward123]

Again, thanks for the great video! For the "xlWhole" option, it appears that "whole" in that context is referring to the whole cell, not a whole word. I'm inferring this from 8:20, where searching for "Ted" using xlWhole finds "Ted" and not "Ted 2" as I would have expected. First, am I correct in this inference, and if so, is there an argument within the Find method to find a whole word within a cell? Thank you!

[@khaledmansour4868]

Hi, I have a question, how can I switch between column for search, for example I can use the ID in my DataBase to find information about someone in my sheet, if I want to use the email column, address, phone number or anything else to find his information, thank you in advance and thank you for all your video, it was great pleasure to watch and learn.

[@khaledmansour4868]

+WiseOwlTutorials Thank you, I appreciate your help, I will try to do the advice you have give it, then I will see, thank you again

[@nunoponts7951]

hello !!! thank you for your great VBA videos !! if you only could make some videos on "data cleaning" using VBA,it would be really helpfull !

[@amandafernandesramospasson5862]

great!! thank you

[@kimhienphan8274]

Hello, Many thanks for your knowledge sharing about VBA. I have one questions relating to find out specific words in notepad. I have lots notepad files and have to look for some specific words in these files. Could you please advise me how to search specific words in the notepad which is opened at the same time? It means I opened the notepad and then VBA just helps me to search some specific words on it (means active notepad). Thanks in advance :).

[@maurocastagnera8949]

Dear Andrew, how long did you take to learn all you know about VBA? I think if when I'll finish watching your videos I've learned half of what you know I'll be happy already, but you're just amazing!

[@maurocastagnera8949]

You are simply the best!

[@StationOnLife]

I am sorry for asking one question. Mr. WiseOwl Training, would you show how I could stop the loop? (What I mean, when the code run, and I found the record, I want to stop the running of the code, but no matter what I do like hit the "escape " button, or trying to close the box, the code keep on running until it reach the FirstFilmCell.). Thank you

[@StationOnLife]

+WiseOwlTutorials THANK YOU for replying to my question SO FAST. I appreciate your taking the time to provide such a detail answer and also had provided me with a link to one of your another tutorial. Thanks again. (Please pardon me for bothering you so much. I AM A BEGINNER at this stuff.)

[@Needforexcel]

Hi Andrew, a great video as usual.. Was wondering if you could make a Video on Regular Expressions (RegExp) in VBA, as it is a fairly complicated subject and I don't think any one can clarify as well as you can..

[@SouVirak]

you can add loop option for user

[@talk2stop]

Top stuff .

[@jthorneification]

One additional ask... what if I wanted to find the value... and have the cell next to the found value selected? (for easier find and data entry)

[@radhelohar4922]

Thank you

[@nhoeksoeng5052]

Thank you for your Great Sharing..Could i ask you questions Pls? When we run code then search box appeared and in search box we just only type English word there but other language such as "Khmer" "Thai"language in search box area we cant..it wil appear symbol ??????? so how can i make search box can search other language??

[@b.petrushchak]

Thank you!

[@RS-zo8yi]

great video..also want to learn how to find data in different workbooks...if you can add small video

[@RS-zo8yi]

WiseOwlTutorials thank you... I will try ND let you know

[@peterkonecny3530]

Hey, I just need to know, how to set SearchRange which is in active Sheet so that it searchs the list of films in any sheet I want. For instance when you have more than 1 sheet and you need to define the specific one. Thanks for reply

[@amitbond1]

Hi Andrew.. is there a way where we can replace all special characters from a cell. For e.g..if A cell contains , : " and these are multiple in numbers..then how should we replace all these together

[@weishenliu2826]

Hi, I tried to run through the code step by step for the find function with user input for search but it just skips through everything right after I clicked "ok". May I ask why I cannot go through step by step using F8?

[@sfwong6042]

Hi, I have got a problem with this. I tried to change the results in "FIlmCell.Value" so that it shows in a cell instead of a message box but the find next function stops after getting the first value. This is what I have done. Do Range ("B2") = FilmCell.Value However it works perfectly if it's a MsgBox. How do I go about it? Thank you!

[@RanjanTimsina]

How can I use Find and Find Next to search from sheet 1 to Sheet n and end on sheet 1 again. Storing all the found value in my destination sheet(n+1). Please answer

[@noahxu3807]

Hello, I tried to run the first simple demo. but I got an error saying object variable or with block variable not set; is there anything I missed pls

[@Vancky-G]

Hi Andrew, I was wondering whether it is possible to nest an array within the Do While loop (I guess with a For loop). My intention is to iterate the content of each matching row as you do in 21:15, with the only difference being to display all matching products/names in a listbox. Can you share your opinion on this? Thank you!

[@Vancky-G]

Hi Andrew, Well, my thought was the following: User inputs a name and then the program searches for that name in the column "Names". Then it tries to match a value in another column against that name (e.g. Company) and then check how many people are in the same company and display all of them in a listbox. Thank you for your help! I really appreciate it!

[@Vancky-G]

Display the whole row of the matching names* (as there will be additional info for each name)

[@Vancky-G]

Hi Andrew, Ok, I tried using the AddItem but when I write (ListBox name).AddItem (the row which has to be displayed).Value, it gives me an "Object required" error. Does its have anything to do with RowSource populations as I left this blank since I don't need it. Thanks!

[@Vancky-G]

Right, I tried to nest this in the Do While loop with the FindNext function but for some reason it stops the effect of the loop (stops it from iterating)

[@Vancky-G]

Or lets say I decided to use the arrays method which worked. Can I display them using your proposed way or can I display a multidimensional array in a listbox (or any)? Sorry for bothering you so much, it's just being a tough program I've been dealing with and I am still new to VBA. Thanks!

[@satishhurjal5144]

Hi, Thanks for your prompt reply and it helps me. again one thing i want to do is... from my computer i want to open remote desktop which is client sever and wants to open textfile which is located at client server to search specific word in that file and if found the specific word i want to email it by outlook. please help me ?

[@daviddeaton7872]

Evening sir, I cannot seem to find an easy to understand method of finding the last occurrence of an item in a column. Any suggestions? Thanks, David

[@daviddeaton7872]

That was perfect! Thank you very much!

[@satishhurjal5144]

Hi, I need your help. I have one scenario its like VBA code should open a notepad and search for specific word in notepad. Is it possible?. please give me reply

[@gerardvaneggermond9967]

Hello, what if I instead of an Input Box indicate a Cell, and instead of a Message Box indicate a Cell or string of Cells to paste in there the value I found, how do I do that?

[@chintanacharya9081]

wonderful Thanks you

[@StationOnLife]

Is there a way a VBA code could be put in so when the record (the film) is found , it would go to the cell that contain the record that was looking for? Thank you.

[@StationOnLife]

+WiseOwlTutorials Thank you so very much for replying to my question/concern so VERY FAST. I appreciate it very much. Your detail informative explanation, the speed of your video, your example used in your demo, etc ...everything is greatly appreciated and extremely helpful. I am not a very bright person; yet, I manage to be able to follow your tutorial without much difficulty. Thanks again.

[@sheikhwasiualhasib4517]

Why FIlmCell value declared here as a Range we can use it like string since it get string value. I don't understand this portion. Please guide me.

[@mat2574]

dear Andrew greetings. I am trying to use again find function after awhile and something goes wrong. I have a column with these number values: 1 - 5 - 21 - 45 - 51. I am looking for number 1 only out from any sort of do/while loop. so just find cell with value "1" and select it. unfortunately it select 21 or 51 instead 1 only. which parameter should I specify? matchcase of lookat xlwhole? thank you

[@mat2574]

hello. please really need assistance for my above issue..... thanks a lot to who will help me....

[@FRANKWHITE1996]

thank you! :)

[@nicerackz]

how do i make a vba to find highlighted cells in a column and jump to each cell?

[@yeuduongtamhuong]

i want use Find methor to find range in anothersheet...but i don't work. Help me!

[@sheikhwasiualhasib4517]

What is Set in VBA? and how to use it?

[@Kingco245]

Range.find starts looking in the cell after the first cell in the search range. This is the default start-search position of the “after” argument. Please, how can I set it to start looking from the very first cell?

[@WiseOwlTutorials]

Hi! You can set the After parameter to refer to the last cell in the range, then the first cell in the range will be the first cell searched. I hope it helps!

[@Kingco245]

@@WiseOwlTutorials yes, it did. Thanks 🙏

[@mrgoat6169]

Range("E8").Value = "Awful" , sorry you don't like that movie Andrew but I must agree with you again on that ! :)

[@steveschlachter7682]

instead of having to move the screens around to show both screens you could just do split screen and show both screens at the same time.

[@hw1875]

Great Tutorial! I just found a problem when I run find: I found VBA does not follow a order when finding a value. For example, I have "ted" at A1, "Ted" at A2, and "Ted2" at A3, my range is B1:B3. And I turned off case sensitive and set xlpart and xlnext as search direction. Then Excel shows Ted at A2 was found, not A1. I am really confused, hope you can help! Thank you!!! my code is : Dim searchRange As Range Set searchRange = Range("B1:B3") Dim filmSelected As Range Set filmSelected = searchRange.find(what:="Ted", MatchCase:=False, lookat:=xlPart, searchdirection:=xlNext) filmSelected.Select

[@hw1875]

Sorry mistyping, "ted","Ted" and"Ted2" are located at B1,B2 and B3, excel shows Ted at B2 was found,not B1 even if I turned off case sensitive

[@hw1875]

It works!!! Thank you sooo much for your quick reply!!!

[@soodashishr]

Thanks for uploading the video. It has been a long time wait - Request for frequent upload of video on subject. Ashish Sood

[@StationOnLife]

I AM STUCKED. NEED HELP. As the search looping thru the list, what I am trying to achieve is to create a Message Question giving the user a choice whether wanting to Exit the search or Find Next. But I don't how and where to add the Message Question in the Loop . Example, Finding the "Ted" name, there's 3 results were found (Ted 1, Ted 2, Ted 3), what was entered in the box was just TED, therefore, when the first result came up was not what I wanted so when the second result which is Ted came up, that' what I wanted and I wanted to exit the search at that time. But with the current code I could not because the search keep looping till the end of the list. Thank you for any help provided. Greatly appreciated.

[@StationOnLife]

+WiseOwlTutorials Thank you for responding to my desperate summon for help so fast!!! Mr. WiseOwlTutorial, the following is what I wrote, and of course, I keep receiving system error message and HAVE NO IDEA WHAT I DID WRONG or HOW TO FIX IT. Would you help to fix it. Thank you very much. If FilmCell Is Nothing Then MsgBox "Song Not Found"Else FirstFilmCell = FilmCell.Address Do FilmCell.Select MsgBox "Song Found" Set FilmCell = SearchRange.FindNext(FilmCell) ButtonClick = MsgBox("Want to Exit?", vbQuestion + vbYesNo) If ButtonClick = vbYes Then If FirstFilmCell = FilmCell.Address Then Exit Do FilmCell.Select Loop While FilmCell.Address FirstFilmCellEnd IfEnd Sub

[@StationOnLife]

+WiseOwlTutorials Thank you so much for your help. I follow your instruction and it solved the problem. Now I just have a last problem to solve. Well, now what I want is when I click the NO button, the search to be continue. So I try to put in the option for the NO option but I got all kind of system prompt error messages. HELP AGAIN PLEASE. This is what I wrote Do FilmCell.Select MsgBox "Song Found" Set FilmCell = SearchRange.FindNext(FilmCell) ButtonClick = MsgBox("Want to Exit?", vbQuestion + vbYesNo) If ButtonClick = vbYes Then Exit Do FirstFilmCell = FilmCell.Address FilmCell.Select Else If ButtonClick = vbNo Then vbCancel Loop While FilmCell.Address FirstFilmCellEnd If

[@StationOnLife]

+WiseOwlTutorials THANK YOU -THANK YOU - THANK YOU. It WORKS and it is perfect exactly what I have tried o accomplish. Your tutorials are wonderful. I can kiss the ground you walk on !!! I am ecstatic right now. Thanks again for taking the time responding to my inquiries and helping thru this....

[@Azam_Pakistan]

Can I have a typed copy of all the VBA statements you are typing?

[@WiseOwlTutorials]

Hi! We do provide downloadable files for many videos (check for links in the video descriptions). Unfortunately, many of our earlier videos don't have a file available, sorry about that.

[@StationOnLife]

I could not find the "TheFindMethod" module. Would anyone help!!!!!

[@mat2574]

hi. I have a problem with the following sub: Sub prova() Dim E As String 'Dim H As Range 'Dim G As String E = range("D3").Value range("F2").Select 'Set H = Worksheets("Sheet1").range("F3:F100") range("F3:F100").find(What:=E, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate End Sub it returns type mismatch error. the problem is related to the range where I want to search variable E, either I specify the range or I set it with a name (H). if I replace range with columns(6) or with cells (it searchs in all spreadsheet) it works. if I specify a range it doesn't work. I don't understand. thank you

[@shantanusrivastava1597]

How do we search if we have to search a sentence in "What:="

[@dailylife5295]

How to find next cell date with userform date picker when match

[@lilcycy7]

Hello. Sorry I keep asking questions you are the only person whose answers are helpful and WORK. So I'm trying to find something... that is unknown but is in a cell on another worksheet.... so for instance...the value in cell A2 in workbook(2), which I may not always know what that value is. I have it so that cell is selected and is the highlighted cell but when I try this code Set Can = Range("A8:A30").Find(Workbooks(1).Activate.ActiveCell.Value, Lookat:=xlWhole)...I get an error... but if I replace that "Activecell.value" part with actual text...it works.... any answers as to why this happens like this?

[@tayotayo2577]

WiseOwlTutorials okay that helps but if I want it to loop so the next time it gets to this line I want it to reference cell A3 and so forth would I have to just keep writing that...or should I use the offset here somehow