0

I'm creating a best practices database at work using Excel and some clever VBA behind the scenes. This is the dashboard/user-interface:

Dashboard

Another sheet contains a database of all these data entries. When the Workbook opens, all of the data entries are placed into an array of custom Objects with Let and Get properties that allow me to conveniently retrieve specific criteria.

For example: Range("A1").Value = DataEntry(1).ProductSegment

All of the search criteria are optional, i.e. a user could search just for a specific author or product segment and leave everything else blank.

Logically, how do I implement multiple optional criteria into my search function? Even more specifically with my custom Object class storing the data.

HerbalTea
  • 41
  • 1
  • 7
  • It's hard to answer this question without knowing how your "database of all these data entries" is set up. One way would be for the data to be setup so that AutoFilter routines could be fed the criteria entered by the user and then you could return the filtered table. Is that how you have it set up? Here's an introduction to that subject: http://www.ozgrid.com/VBA/autofilter-vba-criteria.htm – Tony M Mar 29 '18 at 23:18
  • @TonyM it's just a standard table with headings going across the top. How do you feed non-empty criteria to the AutoFilter routine? If there are 6 criteria, that's 36 possible combinations of criteria to program in. – HerbalTea Mar 29 '18 at 23:36
  • 1
    You will be more likely to get help if you provide samples of the data you have & result you want. A picture tells 1000 words & if the picture is editable those helping you can even copy/paste from it. I can assure you from experience that those who put the effort in to formulating a clear question, get the most help. Furthermore, it's good to demonstrate what you've tried so people know you're serious about getting an answer. In this case, I suggest you look up AutoFiler Multiple Criteria, for example: https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba – Tony M Mar 29 '18 at 23:42
  • I think Tony is onto something, both with the suggestion of AutoFilter as well as the need for more information. A less than ideal approach could be to start with a collection of all objects containing your information from the back end, and cycle through each applying your filter logic for each specified criteria. Each successive filter loop would drop off the undesirable objects. But really, if it is just a sheet on the back end, then just use autofilter. – JG7 Mar 30 '18 at 02:13

0 Answers0