Excel VBA Introduction Part 15a - Find and FindNext
Вставка
- Опубліковано 8 вер 2024
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co... to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/s...
By Andrew Gould
www.wiseowl.co.uk - You can use the Find and FindNext methods in Excel VBA to find values in a worksheet - exactly as the name suggests! This video explains how the methods work, including how to make the search case-sensitive, how to find full or partial matches and how to use Do While loops to find all the instances of something that you're searching for.
By Andrew Gould
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
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! 👏👏👏
Lovely, explained amazingly in very easily understandable manner. Thanks a lot
You're very welcome, thanks for watching!
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.
Couldn't Agree More, Same Here
This is the very first video that motivated me to learn more about vba. It was 5 years ago.
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!
You're welcome Qasim, thanks for watching!
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.
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.
Found Find and FindNext, great one, thanks
Glad you liked it Janez!
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
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 ! !
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!
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!
Yes agreed...the best VBA teaching...simple way...i didnt find it easier to learn than before...thank you so much WiseOwl....
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.
Thank you for your tutorial! I work in Real Estate and It's teaching me how to apply simple code to organize large data.
You're simply the best! I hope you get rewarded for your awesome work.
i know I'm pretty off topic but do anyone know a good site to stream newly released movies online?
@Sutton Matthew i watch on FlixZone. Just google for it =)
@Eddie Roger Definitely, been watching on Flixzone for since april myself :)
@Eddie Roger Thanks, I signed up and it seems to work :) I really appreciate it !!
@Sutton Matthew You are welcome xD
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!!
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.
Watched 16 videos in sequence so far, all videos are so very well done. Thanks so much again!
Your videos are ready useful for all VBA beginners. Well done. Great job.
Your videos are just out of this world! You're simply amazing!!!!
Really excellent presentation. Your VBA knowledge is awesome!
Thanks!. The best Tutorial Series of VBA I ever seen!
I've Downloaded All The playlist 77 Videos Just In Case. Simply You Are The Best. thanks a lot
was stuck on how using findnext, this video has helped me immensely. looking forward in viewing your other videos.
That was amazing! you've saved a company by this simple tutorial
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.
Tremendous work. I recommend this series to my team all the time. Really appreciate the videos!
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
Wow Andrew, you've done a great job again! We really missed your tutorials on vba this last months. Thanks a lot!
Amazing. Thank you Andrew 😊
Thank you very much Andrew. This tutorial was extremely well done!
You are great. Owesome video. Thanks!
Thanks for this video. Its on point and explains the concept very well.
Sir you are the best, thank you providing all this. what you are doing is awesome.
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
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!
Thank you so much for the detailed and very easy to understand video!
that was a another great video indeed. thanks Andrew
Thank you for watching, Sayyad!
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.
NP....Still you are the best...
Fantastic stuff!
Wow! This is great tutorial what I was looking for. Thank you so much
Thanks, very simple and helpful.
very informative, Thank you
great!! thank you
Excellent...Great...!
Very clear and great video!
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.
An epic tutorial. by the way, find function in excel vba is quite weird especially when trying to find a value in number format.
Awesome and amazing! Helped me a ton
Pete, your voice is so relaxing and soothing that its dangerous for me to watch your vids in bed. They are awesome vids though.
Thank you
Top stuff .
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 !
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
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....!!
wonderful Thanks you
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
Thank you!
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..
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.
great video..also want to learn how to find data in different workbooks...if you can add small video
WiseOwlTutorials thank you... I will try ND let you know
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.
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.
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.
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!
You are simply the best!
you can add loop option for user
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)
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!
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.
+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
hello is the loop find next method faster than looping through the range with a for loop?
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
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
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 :).
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 ?
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?
Thanks for uploading the video. It has been a long time wait - Request for frequent upload of video on subject. Ashish Sood
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
thank you! :)
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??
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.
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?
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
+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.)
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
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
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!
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.
+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.
How to find next cell date with userform date picker when match
how do i make a vba to find highlighted cells in a column and jump to each cell?
👍💯
Thanks!
i want use Find methor to find range in anothersheet...but i don't work. Help me!
What is Set in VBA? and how to use it?
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?
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!
@@WiseOwlTutorials yes, it did. Thanks 🙏
I could not find the "TheFindMethod" module. Would anyone help!!!!!
How do we search if we have to search a sentence in "What:="
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.
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
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
That was perfect! Thank you very much!
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
hello. please really need assistance for my above issue..... thanks a lot to who will help me....
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
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
It works!!! Thank you sooo much for your quick reply!!!
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!
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!
Display the whole row of the matching names* (as there will be additional info for each name)
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!
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)
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!
Hm. Something weird happend to me while replicating this lesson. A string which I passed into inputbox was found but it was second occurance in searchrange and then it looped an as last it found the first occurence at the top of searchrange
*****
Thank you for the thorough explanation !
Can I have a typed copy of all the VBA statements you are typing?
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.