Userform find function?

[towners]

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

 

[Clay Canvas]

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

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Dynamic Named Ranges (used as row source for list box)
3		List_Year	=OFFSET(Sheet1!$G$9,1,0,COUNTA(Sheet1!$G:$G)-2,1)
4		List_Month	=OFFSET(Sheet1!$H$9,1,0,COUNTA(Sheet1!$H:$H)-1,1)
5		List_Category	=OFFSET(Sheet1!$I$10,1,0,COUNTA(Sheet1!$I:$I)-1,1)
6		List_Area	=OFFSET(Sheet1!$J$10,1,0,COUNTA(Sheet1!$J:$J)-1,1)
7
8		Source Dataset					Unique Lists (created for list box selection)					Criteria					Final Results
9
10		Year	Month	Category	Area		Year	Month	Category	Area		Year	Month	Category	Area		Year	Month	Category	Area
11		2008	1/1/08	Cat1	Area1		2008	1/1/08	Cat1	Area4		2008	2/1/08	Cat2	Area4		2008	2/1/08	Cat2	Area4
12		2008	2/1/08	Cat1	Area1		2009	2/1/08	Cat2								2008	2/1/08	Cat2	Area4
13		2008	1/1/08	Cat1	Area2		2010
14		2008	2/1/08	Cat1	Area2		2011
15		2008	1/1/08	Cat1	Area2
16		2008	2/1/08	Cat1	Area3
17		2008	1/1/08	Cat2	ARea3
18		2008	2/1/08	Cat2	Area4
19		2008	1/1/08	Cat2	Area4
20		2008	2/1/08	Cat2	Area4
21		2008	1/1/08	Cat2	Area5
22		2009	1/1/09	Cat3	Area5
Sheet1

 

[towners]

Steve,

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

Thanks again

Paul