jamescooper
Well-known Member
- Joined
- Sep 8, 2014
- Messages
- 834
Hello so I have a comboBox which when I select will populate the listbox with all the results. The comboBox options are in a list in column AS, e.g.
Orange
Orange
Orange
Orange
Apple
Apple
Grapes
Grapes
Grapes
Then in column AV adjacent to each of the fruits would be results, so if I select "Orange" it returns all the values from column AV.
The code I have got so far which doesn't quite work is....
Thanks.
Orange
Orange
Orange
Orange
Apple
Apple
Grapes
Grapes
Grapes
Then in column AV adjacent to each of the fruits would be results, so if I select "Orange" it returns all the values from column AV.
The code I have got so far which doesn't quite work is....
VBA Code:
Private Sub ComboBox1_Change()
ListBox1.Clear
With Worksheets("Look-up").Range("AS2:AS" & (Cells(65536, 1).End(xlUp).Row))
Set c = .Find(ComboBox1.Value, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set c = .FindNext(c)
ListBox1.AddItem .Cells(c.Row, 4).Value
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
Thanks.