Getting subsets of data

[Roy Kirkland]

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

 

[Tom Ogilvy]

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