Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting subsets of data
I am working on a sub that will get another cell in the same row from a
selection from a listbox, and place it in another listbox as an item to be selected. In other words, I have one list in a lookup that has several items that belong to each term in the column, and in another column the unique members of the group first selected. If, for example, I had a spreadsheet of desserts, with one column having several entries for each term, such as cake, ice cream, cookie and another column that had items that matched the larger category such as chocolate layer, coconut, lenom poppyseed, vanilla fudge, oatmeal raisin, and I wanted to get a list of say cakes so that I could choose two or three of them without going through the whole column - my idea is that I would use a find to lookup the offset for the variable that takes ListBox1.Value, and then add it to the 2nd lookup using the item as a member of a new collection - is this correct? Should I do this in two loops, one to loop the type of 'dessert' and the second to loop the find method? Should I put the find method in a function that returns only that subset of the database once the category is selected? What is the most efficient way to do this? What I basically want to do is to select a category and then have all items that belong to that category show up in a listbox as selectable items. Has anyone got any code examples of this sort of double listbox? Roy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting subsets of data
Seems like the first listbox has the category selected, so assuming the
columns each start with a single category designation (matching one of the items in the first listbox) and have the members below that Dim rng as Range, rng1 as Range, rng2 as Range set rng = Range(cells(1,5),Cells(1,25)) ' row with category labels set rng1 = rng.find(listbox1.value) if not rng1 is nothing then ' category is found, add items in that column Listbox2.Clear set rng2 = rng1.offset(1,0) do while not isempty(rng2) listbox2.AddItem rng2.Value set rng2 = rng2.offset(1,0) Loop End if -- Regards, Tom Ogilvy "Roy Kirkland" wrote in message ... I am working on a sub that will get another cell in the same row from a selection from a listbox, and place it in another listbox as an item to be selected. In other words, I have one list in a lookup that has several items that belong to each term in the column, and in another column the unique members of the group first selected. If, for example, I had a spreadsheet of desserts, with one column having several entries for each term, such as cake, ice cream, cookie and another column that had items that matched the larger category such as chocolate layer, coconut, lenom poppyseed, vanilla fudge, oatmeal raisin, and I wanted to get a list of say cakes so that I could choose two or three of them without going through the whole column - my idea is that I would use a find to lookup the offset for the variable that takes ListBox1.Value, and then add it to the 2nd lookup using the item as a member of a new collection - is this correct? Should I do this in two loops, one to loop the type of 'dessert' and the second to loop the find method? Should I put the find method in a function that returns only that subset of the database once the category is selected? What is the most efficient way to do this? What I basically want to do is to select a category and then have all items that belong to that category show up in a listbox as selectable items. Has anyone got any code examples of this sort of double listbox? Roy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating subsets | Excel Discussion (Misc queries) | |||
How can I rank values in multiple subsets using a single formula? | Excel Discussion (Misc queries) | |||
Ranking values in multiple subsets using one single formula | Excel Discussion (Misc queries) | |||
Manipulating subsets of large datasets | Excel Worksheet Functions | |||
Xcl-create subsets of a set of numbers w/o duplicating the subset | Excel Worksheet Functions |