Multiple Search Macro using VBA

[G.ExcelConnundrums]

Hi Guys,

I am trying to create a multiple search bar in Excel to search 8 worksheets. (all the ranges/columns in which the search needs to be performed have the same name as the search criteria. I.e: type in the search criteria is a "type" range/column.)

I want to look over 7 different search criteria
Ref Number (String)
Last Name (String)
First Name (String)
Alpha (rating system I use comprising of A for low, AA for medium, AAA for high) (String)
Type (String)
Post Code (String)
and Review Date (int)

The results should be listed in a table beneath the Search bar which has the same headings as the search criteria.

The Columns in the other worksheets are the same as the names of the search criteria.

What I would like to do is be able to search one of the criteria, and for the other cell values to be completed. But, for example, if I have more Johnsons than just one, I would like them to be listed as well. As they would if you were to search a database for all Johnsons...

How would I go about doing something like this?
I am only a very new programmer and I would appreciate the help...

If you could comment the code you put as well, I would be really happy as I'd like to understand how the code works..

Thank you very much
Gustav

 

[Saagar]

Gustav - I did not understand the sentences in red. Can you explain what you mean with examples and/or screenshots of your worksheets?

- What is the difference between the data in the 8 worksheets?
- Can't you put all the data in the same worksheet and use "filters" to search?

Hi Guys,

I am trying to create a multiple search bar in Excel to search 8 worksheets. (all the ranges/columns in which the search needs to be performed have the same name as the search criteria. I.e: type in the search criteria is a "type" range/column.)

I want to look over 7 different search criteria
Ref Number (String)
Last Name (String)
First Name (String)
Alpha (rating system I use comprising of A for low, AA for medium, AAA for high) (String)
Type (String)
Post Code (String)
and Review Date (int)

The results should be listed in a table beneath the Search bar which has the same headings as the search criteria.

The Columns in the other worksheets are the same as the names of the search criteria.

What I would like to do is be able to search one of the criteria, and for the other cell values to be completed. But, for example, if I have more Johnsons than just one, I would like them to be listed as well. As they would if you were to search a database for all Johnsons...

How would I go about doing something like this?
I am only a very new programmer and I would appreciate the help...

If you could comment the code you put as well, I would be really happy as I'd like to understand how the code works..

Thank you very much
Gustav

 

[G.ExcelConnundrums]

The different spreadsheets are for different types of policies: E.g: Mortgages, Pensions, Income Protection, Etc..

so for example:

the income protection document has, as column headers:
Ref number, First Name, Last Name, Postcode, Alpha, Type, Start Date, End Date, Review Date

the mortgage spreadsheet has:
Ref number, First Name, Last Name, Alpha, Type, mortgaged address, Post code, Amount, Provider, Scheme, Redemption Date Ends, Review Date

So you can see that there are different columns in each spreadsheet, but I want to search the "common" columns..

Does that answer your question somewhat?

 

[Saagar]

Yes. I am short on time this weekend. I will get back to you next week if somebody doesn't help you before then.

 

[G.ExcelConnundrums]

Thank you very much Saagar...

If you would like, when you have time, I can send you the spreadsheet.. however there wont be any data in it as the data is confidential to my company.

Would that help as well?

Also, could you give me some tips on learning VB?

G

 

[Saagar]

Spreadsheet with sample data will help.

If the search fields Ref number, Postcode, Alpha, Type, Review Date do not compromise your confidentiality, leave those in. You may delete rest of the data. Or, just populate the fields with your own sample data. If you can fill up the First Name and Last Name fields with names Like F1, F2, F3... and L1, L2, L3... and create data indicative of your actual data, appearing in multiple sheets, that will be helpful. I will send you a PM with my email address.

Glad that you are receptive to learning VB. Tips:
- Scour the message board and web. I don't know any good web references but there are plenty if you search.
- Excel help and microsoft website have lot of information.
- Create macros with the built in macro recorder (Tools > Macro menu in Excel 2003) and capture your normal excel activities and review the code generated.

Good luck!