Philip has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Searching for data based on a cell value
Hi there,
I've got a spreadsheet which is arranged a bit like a Gannt Chart, with Week numbers across the top and products on the left. There are several columns of data per week for each product.
The spreadsheet currently has two macros, which I did not write.
They are both run from buttons on a separate Worksheet called Generate.
The first macro copies data from the Schedule worksheet into a new spreadsheet, if the value in a certain cell of the Schedule matches up with the designated cell in the Generate worksheet. So I type in which week I want to find data for, and it copies across the relevant data to a new spreadsheet.
Currently this only works if I enter a number (e.g. 7) in Cell C3 of Generate. I want to be able to enter text as well (e.g. c7), and search for a different set of data.
Would this be fairly simple to do?
Can I attach the spreadsheet so you can see how it works?
RE: Searching for data based on a cell value
Hi Philip,
Thank you for your post. Please send examples of the spreadsheets and macros to forum@stl-training.co.uk so that we can have a look at the data and the code. This way, we can help you more effectively.
Kind regards
Marius Barnard
Excel Trainer
RE: Searching for data based on a cell value
Hi
I've emailed the spreadsheet with the macros in the hope that you can help.
I've tried to explain in the email what I want to achieve.
Look forward to your reply.
Philip Conrad, Burston Nurseries.
RE: Searching for data based on a cell value
Hi Philip,
Thank you for the files. I have referred your post and forwarded the files to one of my colleagues who will be better able to decide how to go forward with your question.
Kind regards
Marius Barnard
RE: Searching for data based on a cell value
Hi Philip,
No this will not be fairly simple to do.
It depends on how you would like the output, which criteria you want in the search. If you look at the code in the example workbook it is very specific what the macro is doing with the input (week number). Which information you get in the output worksheet. You will need to do something similar for all the text criteria you want to use as input to the macro.
The source data is not setup for Pivot Table use (it is not a flat list). If the source data where in a flat list it would be more simple.
If it is the same text criteria you want to use every time you can create a userform and call custom views based on the auto filter.
I have attached a workbook with a simple example. If you press the filter tool button you will load a userform where you can select between 2 filter options (it can be any number of options). Click the drop down list (the combo box) and select an option. You can create a new sheet with the filtered data if you click copy to new sheet.
I hope that the code in the workbook can give you some good ideas.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
