Listbox Question - XLBO Can U Help?

[BatFace]

After the obvious answer to my previous question regarding filtering by set parameters I have put 2 listboxes on my user form....Because I'm a newbie to VBA playing with pivot tables was a wee bit daunting!...Here's the code I have:

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim NoDupes2 As New Collection
Dim i As Integer, j As Integer
Dim k As Integer, l As Integer
Dim Swap1, Swap2, Item

Set AllCells = Range("C1:C3000&quot

' find and sort use codes

On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell

On Error GoTo 0

For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

For Each Item In NoDupes
UserForm1.UseCode.AddItem Item
Next Item

' find and sort the age
Set AllCells = Range("j1:j3000&quot
On Error Resume Next
For Each Cell In AllCells
NoDupes2.Add Cell.Value, CStr(Cell.Value)
Next Cell

On Error GoTo 0

For k = 1 To NoDupes2.Count - 1
For l = k + 1 To NoDupes2.Count
If NoDupes2(k) > NoDupes2(l) Then
Swap1 = NoDupes2(k)
Swap2 = NoDupes2(l)
NoDupes2.Add Swap1, before:=l
NoDupes2.Add Swap2, before:=k
NoDupes2.Remove k + 1
NoDupes2.Remove l + 1
End If
Next l
Next k

For Each Item In NoDupes2
UserForm1.Age.AddItem Item
Next Item

UserForm1.Show

End Sub

Ok so my question now is - is it possible to have the age list driven by the use code? Once the use code is selected, only the ages listed for that use code are displayed in the age list box?

Thanks again for your help - I'm learning very quickly thanks to the assistance provided by the gurus here!

 

[xlbo]

Obviously, you bin to J-Walk.com - I recognide that deduper for list boxes ;-)

Anyway, to get corresponding Age values for UseCode - problem is, you've probably got quite a lot of data yeh ??

'cos the 2ways that spring to mind are - filter your data set using autofilter and your UseCode - then sort (by age value) and loop thru the selected cells, adding to your listbox as you go (once sorted, you can use If(Range("A" & i).text = range("A" & i-1).text then next i else 'add to listbox
Depending on the number of formulae in your dataset, this will either take "no time" or ages. The other way is to just loop thru the whole data set and where loop cell = UseCode, use offset to look at the corresponding AgeValue and add to list if not already there.

Hope this points you in the right direction
Geoff

 

[BatFace]

Thanks Geoff - I'll try that when I get back to work..Public Holiday here today ...I actually have j-walkenbach's "power programming with vba" book - which is my other source of learning - glad u noticed 'cos I said I was a newbie and then felt like a cheat putting all that fancy code in!

Thanks again for your help!