I need to create a macro to search multiple worksheets in a workbook for keyword then copy entire row of every keyword found onto a new worksheet? I have created a code that for some reason only returns the last keyword found on the first worksheet..? Here is what i have come up with so far:
Please Login or Register to view this content.
Any help would be greatly appreciated
Last edited by arlu1201; 11-23-2012 at 01:39 PM.
Hey there
Make sure you read the forum rules http://www.excelforum.com/forum-rule...rum-rules.html - please make sure you place code tags around your code as it makes it difficult to read otherwise.
A few things - the problem is due to not iterating through the ranges containing the word you are searching for. You will only get one result - you write the sheet name to A3 in Results Sheet and then find the next free row which will always be A4 - to do what you want is a little more complicated. Without a sample workbook I do not know how your data is set out (is the data always between column A & I for example). A sample workbook always makes it easier to give you a solution.
Anyway I have attached a sample workbook - you can try it out and try to search for (help or test). You need to have a function that finds all the ranges containing the row with the search word. Also you rarely every need to Activate or Select in your code - you can condense this down. Finally make sure you exit the sub prior to the error trapping otherwise the error message will always be raised.
Have a look at the attached workbook - if you need more help or clarification just post any problems you are having.
Please Login or Register to view this content.
Last edited by smuzoen; 06-20-2012 at 10:22 PM.
Hope this helps.
Anthony
Pack my box with five dozen liquor jugs
PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated?
Anthony,
Sorry, I am new to all this. This is the first VB code I have written and my first time using this forum. I have now attached a sample workbook similar to what I am trying to write this code for. I will mainly be searching the C column for a keyword to find similar projects so that I can group them all together on a new sheet ("Search Results"). Each keyword will likely be found on multiple sheets and also multiple times within each sheet. I have also attached what I would like the results sheet to return and look like after running the macro. To have the project category included in line with the corresponding project title would be nice, but is not critical and im not even sure if that is possible.
I tried to run the macro included with your sample workbook, but it seems to only return the sheet names and not the entire row of the keywords found. I am a bit confused with what all exactly the FindAll function you added does. I will continue to try to figure out your code more but as of right now I am not having much luck.
Thank you very much for responding and for the help
I have changed the code to match your workbook. In the information sheets (Place 1, Place 2 etc) if this how you have the information stored then the code will produce the search results formatted the way you had in your sample. The FindAll function is designed to find EVERY occurrence of the keyword you are searching for and this function will allow you to do this by passing the required parameters and if you want the optional parameters. What it returns is all the ranges that contain the keyword so you just need to iterate through this to find each discrete range and then write the data to the output sheet.
Attached is the revised workbook.
If you have any problems or questions just ask.
PS: If this is the first lot of VBA code you have written you were certainly on the right track and I am impressed for a first go. Well done!
Anthony,
Thank you. This code is awesome, I am very impressed. It does the exact task that I need it to do. Just a couple small questions:
1. I wanted to add a msgbox to display how many results are found each time, but I cant figure out where i need to add the variable to count the loops. I thought i would just have to add a total = total + 1 before the "next keyWord" to counthow many keyWords it finds but that does not give me the right number. I also saw you already count the posA but that number ends up being every line used in the search results which isnt what i want bc each new Segment line is not actually a result. Any insight on this?
2. The last place (place3) always shows up under Segment on the Search Results even if no keyword is found in it. This isnt that big of a deal but I just cannot figure out why it does that? For example, in the Place 3 sheet delete the word "vessel3" off "tank and vessel3" so that it just says "tank and". then run macro and search for keyword vessel and you will see what I mean.
3. Is there an easy way to switch between search results including the entire string entered and partially containing it. Right now it finds partial strings which is what i need for the most part. But is there a simple way to change it so that if for example I search "rod", projects titles containing "product" dont show up in the results. I feel this should be relativly simple but I cant seem to figure it out. If there is not any easy way to switch back and forth between the two options then the way it is now is fine.
Thanks again for all the help thus far.
I have added a message box that outputs the number of results for you. I have fixed the problem where if there are no results that the last Sheet name (in this case Place 3) is not written to the Results sheet. I have added a question to ask if you want to search for part of the string or the entire string. So you can search for "rod" or "products" and qualify if you want the entire string searched or only search in part.
If you have any questions just ask.
Last edited by smuzoen; 06-23-2012 at 03:47 AM.
Anthony,
Thank you very much for all your help. The macro works perfectly and does everything exactly how I was trying to get it to. Now on my on time I just need to try to figure out how you got it work like that.
Thanks again. This forum is awesome.
Hello! Was reading the tread and was hoping for some help. I am looking for something a lot this except when it finds the result no matter what column it is in I need the entire row to be displayed. Also If it could display the column headers too that would be cool if possible. The column headers are different on each spreadsheet.
Thanks in advance.
rjfnc,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Sorry Arlette. New to the site and the code was close to what I was looking for. I set up a new thread. Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks