Userform find function?

towners

Board Regular
Joined
Mar 12, 2009
Messages
225
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I have a dataset that I want to publish and allow users to select a range of records based on several criteria that are columns within the dataset. i.e. Year; Month; Category, Area.

My thoughts are to create a userform with list boxes that the user can select from and press a command button that runs a macro that finds all rows that match the criteria selected and copies them into a seperate worksheet for view or printing. In the same style as the auto filter works.

I have a couple of questions...

1) Is this the best method?
2) How do I code the dynamic named range (that i use as the row source for the userform list boxes) to display the unique values from the particular columns of the dataset - to provide the user with a relevant selection criteria. i.e only display months that are actually in the dataset rather than all 12?

Thanks all
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Towners,

Here's one method you could consider. This method uses Excel's Advanced Filter. Here's how the method could work.

1. Create a unique list of years from your dataset with code similar to macro below called "Unique_Year" using the results to populate your first list box

2. Create a unique list of months by triggering code based on the selection of the user from list box (having the user's selection of year entered into range("L11")) and then running called "Unique_Month" to create data to populate your 2nd list box

3. Create a unique list of Categories by triggering code based on the selection of the user from list box (having the user's selection of month entered into range("M11")) and then running code called "Unique_Category" to create data to populate your 3rd list box

4. Create a unique list of Areas by triggering code based on the selection of the user form list box (having the user's selection of Category entered into range("N11") and then running code called "Unique_Area" to create data to populate your 4th list box

5. Finally, enter user's selection of Area into range("O11") and then run the code called "Selection_Results" to create your final list of data based on all criteria selected by the user. You can then do whatever you'd like with the selection. Copy it to another sheet, etc.

I'm assuming based on your mention of user forms and dynamic named ranges you're familiar with both. I've included the dynamic named ranges below to use to populate the user list boxes. If you have any questions on how to implement this with the user form please let me know.

Hope this helps,

Steve

Code:
Sub Unique_Year()

    Range("B7:E47").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "G7"), Unique:=True
End Sub
Sub UniqueMonth()

    Range("B7:E47").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "L7:L8"), CopyToRange:=Range("H7"), Unique:=True

End Sub
Sub UniqueCategory()

    Range("B7:E47").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "L7:M8"), CopyToRange:=Range("I7"), Unique:=True

End Sub
Sub UniqueArea()

    Range("B7:E47").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "L7:N8"), CopyToRange:=Range("J7"), Unique:=True
End Sub
Sub SelectionResults()

    Range("B7:E47").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "L7:O8"), CopyToRange:=Range("Q7:T7"), Unique:=False
End Sub
Excel Workbook
ABCDEFGHIJKLMNOPQRST
1
2Dynamic Named Ranges (used as row source for list box)
3List_Year=OFFSET(Sheet1!$G$9,1,0,COUNTA(Sheet1!$G:$G)-2,1)
4List_Month=OFFSET(Sheet1!$H$9,1,0,COUNTA(Sheet1!$H:$H)-1,1)
5List_Category=OFFSET(Sheet1!$I$10,1,0,COUNTA(Sheet1!$I:$I)-1,1)
6List_Area=OFFSET(Sheet1!$J$10,1,0,COUNTA(Sheet1!$J:$J)-1,1)
7
8Source DatasetUnique Lists (created for list box selection)CriteriaFinal Results
9
10YearMonthCategoryAreaYearMonthCategoryAreaYearMonthCategoryAreaYearMonthCategoryArea
1120081/1/08Cat1Area120081/1/08Cat1Area420082/1/08Cat2Area420082/1/08Cat2Area4
1220082/1/08Cat1Area120092/1/08Cat220082/1/08Cat2Area4
1320081/1/08Cat1Area22010
1420082/1/08Cat1Area22011
1520081/1/08Cat1Area2
1620082/1/08Cat1Area3
1720081/1/08Cat2ARea3
1820082/1/08Cat2Area4
1920081/1/08Cat2Area4
2020082/1/08Cat2Area4
2120081/1/08Cat2Area5
2220091/1/09Cat3Area5
Sheet1
 
Last edited:
Upvote 0
Steve,

Thank you for the suggestion. Yes, I think that is the way to go, is work in progress...

Thanks again

Paul
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top