Solved search multiple words in excel

I need to search 10 words in 10 excel files at once. Can anyone give me a macro for doing this? For example i want to search london,doha,delhi in 10-15 files in a folder at once. Is this possible with a macro?

It's probably possible, but I'm sure there are lots of things you haven't told us. There's no sense in writing code until we have all of the information.

What do you want to happen after the word or words are found?What do you want to happen if no words are found?Should the code stop after the first word is found or does it have to search every file for every word?Does the code have to search every cell in every sheet in every file or is the search area limited in some way?

As you can see, there is a number of things we need to know before we can offer any code that will fit your needs. Please give us all the details of what you are trying to do.

In addition, unless you know how to write/modify VBA code, please don't try to "keep it simple". What often happens is that we end up writing code to address the "simple" requirements, then we have to add code or modify code to meet additional requirements.

If you tell us everything we need to know right up front, it's easier on us.

Presently I am suing this code for searching. But I can search only one word at a time. But I want to search 10 or more words at once and the result should be displayed in a sheet with link. I tried to modify the code but every time I faced a lot of errors. I am not an VBA expert and it is difficult for me to find out a solution myself. My requirement is : 1. I have to select the folder. All files in that folder has to be searched for the words given in a range. (like the below code)2. If no result found a blank sheet with headings may be displayed. (like the below code)3. Code should search for all words in all files.4. All the results to be displayed in a single sheet.-----------------------------------Sub SearchWKBooks()Dim WS As WorksheetDim myfolder As StringDim Str As StringDim a As SingleDim sht As Worksheet

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then repost your code so that it looks similar to the example found via that link. Thanks!

Second, I'm a little curious as to why you didn't post the code that you already had and ask that it be modified to fit your new requirements. It sure would have been a waste of time for someone to have started from scratch when the most of the work has already been done.

In any case, please read the following instructions and then repost your code using the pre tags as described.

Thank you very much for the guidance. First I did not post the code because I thought it is not necessary. I have reposted the code. If I get the code as per my requirement I can save 10 minutes in my office work. Please help. Thanks in advance.

Not to belabor the point, but in order for us to have answered the question you posed ("Can anyone give me a macro for doing this?") we would have had to start from scratch and write every step of the code. It would have been a lot of work to duplicate what you already had. Once I saw what you already had, it was a simple matter of adding a few more instructions and doing a little editing. That was a lot less work than starting with nothing and writing the entire macro from the beginning.

BTW...I noticed that you don't have any comments in the macro that you posted. Comments are very useful, not just when posting code for others to read, but also for the author. A wise man once said:

"Code tells you how, Comments tell you why."

Imagine coming back to this code a year or more from today and trying to remember exactly what each section does and why you did it that way. Getting into the habit of adding comments makes it so much easier to understand what the code is supposed to be doing, not only for the author, but also for those they may need to read and/or modify the code.

Finally, I don't know if you use any debugging techniques when your code presents errors, so I'd like to offer this tutorial:

These debugging techniques are useful for not only finding problems with your own code, but also for "reverse engineering" code that you find on the internet or in someone else's workbook.

OK, so back to your question...

What I did was use a 10 element Array "Str(10)" to accept up to 10 string inputs. The code loops through the "InputBox" section gathering the strings from the user and building the Array until the user clicks Cancel.

Once the Array has been built, the code loops through the array, searching for each array element. In other words, instead of searching for the single string "Str" like it did in the original code, it now searches for "Str(0)", then "Str(1)", then "Str(2)", etc.

I also added a column to the output sheet to display the string that was found in the same row as the other information that you were already displaying. e.g.

Thank you very much for your valuable suggestions. I am just starting learning VB and I know I have to learn a lot. The code works perfectly as per my requirement. But I want like search. Even though LookAt:=xlPart , only the whole word search is happening. And another one - is it possible to select a range instead of typing words one by one?Thank you once again for spending your valuable time for answering my queries.

I want to search a cell containing my search string. for example if my search string is London, cell containing London-UK is also to be included in the search result. I thought that xlpart will do this. But in the code even though the LookAt:=xlPart the cell with exact match the string are searched. I cant understand the mistake in the code. Of course, i can handle this by adding * as prefix and suffix in my search string. But if the code will do that it is better.Another one, selecting a range of search strings at once instead of selecting or typing one by one. For example if i have to search for 10 strings, i will put them in cell A1:A10 and in search string input box i have to select the range. Whether this code can be modified like this?Waiting for your valuable reply.

I created 2 workbooks and included the following strings in various cells in various sheets:

London-UKLondon is niceUK-London-UKppppLondonppppLondonLondon BridgeBridge London Bridge

I used a search string of London and the code found every one of the above strings.

Perhaps you are confused by the output that my code is presenting? The extra column I added returns the search string, not the contents of the cell where the search string was found. I did this so that you can see which sheet contains which search string when using multiple search strings.

If you want to see the contents of the cell that was found, simply change this instruction:

WS.Range("D4").Offset(a, 0).Value = Str(elmNum)

to be

WS.Range("D4").Offset(a, 0).Value = c.Value

If the code is not finding the cells that contain the search string when it is only part of the cell, then there may be something going on with your data, not with the code.

In the meantime, I will work on the "selection" question that you raised.

Yes, Working perfectly. Thank you very much. Now I am worried with how to modify the code for selecting range as multiple search string.I know it is not fair to ask everything. I have learned a lot from your guidance. If the code modified for selection of range , then this code become very useful to me. I am very grateful to you for sharing your knowledge.

I have a question about your formula - so I have a list of keywords I need to search for on my excel sheet. Example of my keywords are (white, red, black, green, yellow, blue) where on this formula would I change to include the colorls I wanted to search for?

If the cell contained part of the "red" like the word Reddick and Reddit, I would like to show that red had matches for Reddick and reddit.

The information on Computing.Net is the opinions of its users. Such
opinions may not be accurate and they are to be used at your own risk.
Computing.Net cannot verify the validity of the statements made on this
site. Computing.Net and Compnet Ventures, LLC hereby disclaim all responsibility
and liability for the content of Computing.Net and its accuracy.