Excel VBA search multiple sheets

[jonobono8888]

Hi All,

I have this workbook which contains one summary page and numerous other worksheets which have rows containing different restricted substances according to regulations. Each of these substances have different identification numbers (CAS), EC Numbers and Regulations which are labelled as separate columns. (i.e. Col A = Substance, Col B = CAS, Col C = EC #, etc). Note: Not all worksheets has the same info – so some may have extra data like description, websites where you can find it, some may have less (it’s not consistent since it’s copy and pasted from different sources).

Essentially, I’d like to have a search function on the first worksheet “Sheet1” (so exclude summary page) which would create a list of data returned from all those regulation worksheets. There will sometimes be more than one incidence of the substances or EC number in different worksheets. This is good – I want to see if the substance is in multiple regulations. If the substance or EC number is found in any of the worksheets, I’d like the macro to return specific information from worksheet – namely the substance, CAS Number, EC Number (if available), and the regulation it’s from (another column).

I’ve googled searchable lists and other macros which do similar things to this but I think the main problem will be:

1. Skipping the summary page for searching
2. Returning specific information from each column for each worksheet and putting it into the right spot on the main search page.

If anyone could help me out I’d be ever so grateful. Thanks! If you need clarification please let me know and I’ll get back to you. If you need me to send the file, please pm me!

 

[gallen]

Re: Excel VBA search multiple sheets help

The main problem which I'm experiencing is that if I search a term in the Main database which is shown in multiple rows (e.g. frame, project name, etc) only one row of that data will come up (even though i know there should be at least 20+ rows which contains that word).
I'm thinking it may be an issue with this line?

'continue searching the sheet for more instances
        Set rFind = sh.UsedRange.FindNext(rFind)
         'if found in a different loaction than the first one, add that row too if the row number is different
         If rFind.Address <> sFirst And rFind.Row <> lCurrentRow Then GoTo AddRow

Based off your response - I think you know what I mean and what the problem is but it's just that the problem isn't replicating for you?

Correct. The issue must be with these lines. When I tested I made several sheets with dummy data. I placed an instance of the search-word located in A1 on several rows of each of the sheets. The reason I put the last column showing where the term was found was to easily see where each row of the results table originated from.

I will try again to recreate your issue.