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.
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.
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!
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
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.
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!
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 ! !
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.
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
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.
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.
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.
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!
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....!!
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!
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 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!
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!
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?
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.
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?
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
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
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.
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
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
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.
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!
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
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 ?
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!
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??
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 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.
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 :).
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 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
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
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..
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.
+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
+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
+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....
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
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.
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!
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
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?
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
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! 👏👏👏
This is the very first video that motivated me to learn more about vba. It was 5 years ago.
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
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.
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!
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
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.
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....
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 ! !
Watched 16 videos in sequence so far, all videos are so very well done. Thanks so much again!
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'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
Your videos are ready useful for all VBA beginners. Well done. Great job.
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 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.
Lovely, explained amazingly in very easily understandable manner. Thanks a lot
You're very welcome, thanks for watching!
Your videos are just out of this world! You're simply amazing!!!!
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
was stuck on how using findnext, this video has helped me immensely. looking forward in viewing your other videos.
Tremendous work. I recommend this series to my team all the time. Really appreciate the videos!
Wow Andrew, you've done a great job again! We really missed your tutorials on vba this last months. Thanks a lot!
That was amazing! you've saved a company by this simple tutorial
I've Downloaded All The playlist 77 Videos Just In Case. Simply You Are The Best. thanks a lot
Really excellent presentation. Your VBA knowledge is awesome!
Thanks!. The best Tutorial Series of VBA I ever seen!
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.
Found Find and FindNext, great one, thanks
Glad you liked it Janez!
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...
Thank you very much Andrew. This tutorial was extremely well done!
Sir you are the best, thank you providing all this. what you are doing is awesome.
Thank you so much for the detailed and very easy to understand 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.
Thanks for this video. Its on point and explains the concept very well.
Wow! This is great tutorial what I was looking for. Thank you so much
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!
Amazing. Thank you Andrew 😊
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....!!
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!
You are great. Owesome video. Thanks!
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 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!
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?
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.
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 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?
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
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
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)
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.
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
Thanks, very simple and helpful.
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 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
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.
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!
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
An epic tutorial. by the way, find function in excel vba is quite weird especially when trying to find a value in number format.
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 ?
Very clear and great video!
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 🙏
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??
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.)
Pete, your voice is so relaxing and soothing that its dangerous for me to watch your vids in bed. They are awesome vids though.
i want use Find methor to find range in anothersheet...but i don't work. Help me!
Fantastic stuff!
how do i make a vba to find highlighted cells in a column and jump to each cell?
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!
very informative, 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,
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 :).
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.
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 !
I could not find the "TheFindMethod" module. Would anyone help!!!!!
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....
Awesome and amazing! Helped me a ton
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
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..
How do we search if we have to search a sentence in "What:="
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.
+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
+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
+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....
you can add loop option for user
What is Set in VBA? and how to use it?
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!!!
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.
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!
great!! thank you
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
How to find next cell date with userform date picker when match
Excellent...Great...!
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 !
Thanks for uploading the video. It has been a long time wait - Request for frequent upload of video on subject. Ashish Sood
Thank you
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?
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
Thank you!
Top stuff .
Is this the same as match
@@WiseOwlTutorials ok will the application worksheet work with any formula
@@WiseOwlTutorials do you how to put more formulas into one cell.
@@WiseOwlTutorials ok do you have reddit
@@WiseOwlTutorials basically im trying to return the matching numbers that match whats in the row in the columns
wonderful Thanks you
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.