Macro search entire workbook

[nickmax1]

Please see attached example

I have several worksheets with the format mmm yyyy in which I want to search for an account name. Very similar to :
http://www.excelforum.com/excel-prog...readsheet.html

I want it to be able to search for a partial hit as well ideally. My macro would be slightly different to the above as I am searching many worksheets, which will be ever growing.
I need the macro to return the account as well as returning the name of the worksheet tab that it resides in.

For example if I type "Char" I should get two hits for Charoen so it looks like this

Account Renewal Month
Charoen Jan-12search.xls
Charoen Feb-12

[MarvinP]

Hi nick,

Your pointer to the other answer does an advanced filter of a single list which is much different than a Search.

Did you know you can search the entire workbook for a string or partial string and click on Find All and it will show what you want?
Look at http://office.microsoft.com/en-us/ex...001230225.aspx and/or
http://www.brainbell.com/tutorials/E...d-replace.html

[nickmax1]

thanks, yes I can see the difference between the filter of a list and a search, but I do need to have a way to display not just the name i am searching for but accompanying information belonging to it in adjacent rows.

[MarvinP]

Hi Nick,

Do a Find and change the options to WORKBOOK and click on FINDALL. Then click on the list of found and see if that isn't good enough for you. It will take you to what it has found. I'm not sure you tried the find all and click on the list yet. It sounds like you want a function that is already in Excel and just don't know how to use it effectively yet.

[nickmax1]

I am perfectly aware of the Find function but it doesnt replicate what I want to see in a search. It just gives me a list of where my cell ranges are - it doesnt give me any other information that is attributable to the cell in question.

[watersev]

hi nickmax1, please check attachment, hope that helps

[nickmax1]

watersev,
thanks a lot but its not quite working.

I should have deleted the Source colomn as that is complicating matters, but your search macro is finding the right account name but it is putting it in the header "Renewal Month/Year" instead of the "Account" header.
Under "Renewal Month/Year" I was hoping the macro would return the name of the worksheet the data resides in.

?

[watersev]

please check attachment

[nickmax1]

you sir, are a legend.

Is the SEARCH button necessary, can the macro be run when something is entered in the search box? no matter if not!

[watersev]

it can but I would stick with this option. If you insist it can be done that way

[nickmax1]

thats fine...

I have entered your code into my "master" spreadsheet but now i get an autofilter error??

Activity test.xlsm

[watersev]

please check attachment

PS. Search is made on visible sheets only.

[nickmax1]

watersev you are some kind of a genius.

thank you so much. rep added.

[nickmax1]

Watersev you kindly helped me with a search macro that looked through my workbook....I dont know what has changed but it doesnt work anymore..... It returns the right result but also returns other data???

Activity test.xlsm

[watersev]

if you change data layout be prepared to amend the code as well

[nickmax1]

sorry watersev that didnt quite work, still returns random entries (as well as the correct ones)

[watersev]

I do not get any mistaken data as a result of the search

[nickmax1]

that is strange....but if select "christ" i get Christchurch and 7 other entries.....

[watersev]

please check attachment, it should be ok now

[nickmax1]

fantastic!! not sure how you did it but this works - thank you so much

[nickmax1]

watersev,

A very small thing, but before the macro is run rows 17 on all sheets (except start, template, brokers, summary) are hidden....after the macro is run it unhides row 17 from the other sheets. Any way we can stop that from happening???

[watersev]

Please Login or Register  to view this content.

[watersev]

please check attachment, press Search button

[nickmax1]

i havent gotten round to testing this on my master spreadsheet but based on the test file it is absolutely perfect....thank you so much!!

[nickmax1]

perfect. Its quite a different code to your original, but however it does it , it does it very very well. thanks again!

[nickmax1]

Watersev,

Your code (as always) runs brilliantly. I was wondering if there was any way I can identify the cells in which the results come from and in the next cell to the hyperlink have the output say:

If the hyperlinked cell is greater than 190 then: "This is an AP/RP"
Otherwise say "Normal Premium"

This I would imagine could be done without a macro but i dont know of a formula that can "read" the hyperlink and extract the cell address to come up with that...