Populate Listbox based on selection of combobox1

[jamescooper]

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....

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.

 

[jamescooper]

So I have improved it to this, but it isn't quite aligning.

It is 1 row out each time?

Private Sub ComboBox1_Change()

       ListBox1.Clear
       With Worksheets("Look-up").Range("AS1:AS" & (Cells(1048576, 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

 

[jamescooper]

Any ideas here please?

 

[dmt32]

Any ideas here please?

Hi,
untested but see if this update to your code resolves your issue

Private Sub ComboBox1_Change()
    Dim rng As Range, c As Range
    Dim Search As String, firstaddress As String
    
    Me.ListBox1.Clear
    Search = Me.ComboBox1.Value
    If Len(Search) = 0 Then Exit Sub
    
    With Worksheets("Look-up")
        Set rng = .Range(.Range("AS1"), .Range("AS" & .Rows.Count).End(xlUp))
    End With
    
    Set c = rng.Find(Search, LookIn:=xlValues, Lookat:=xlWhole)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            ListBox1.AddItem c.Offset(, -41).Value
            Set c = rng.FindNext(c)
            If c Is Nothing Then Exit Do
        Loop While c.Address <> firstaddress
    End If
End Sub

Dave

 

[jamescooper]

Not quite, what is the significant of -41?

Thanks.

 

[Norie]

James

Where is the date located?

P.S. The -41 in dmt32's code might have something to do with this.

 

[jamescooper]

This works though - thanks for your help!

Private Sub ComboBox1_Change()
    
    Dim rng As Range, c As Range
    Dim Search As String, firstaddress As String
    
    Me.ListBox1.Clear
    Search = Me.ComboBox1.Value
    If Len(Search) = 0 Then Exit Sub
    
    With Worksheets("Look-up")
        Set rng = .Range(.Range("AS1"), .Range("AS" & .Rows.Count).End(xlUp))
    End With
    
    Set c = rng.Find(Search, LookIn:=xlValues, Lookat:=xlWhole)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            ListBox1.AddItem c.Offset(, 3).Value
            Set c = rng.FindNext(c)
            If c Is Nothing Then Exit Do
        Loop While c.Address <> firstaddress
    End If

End Sub

 

[dmt32]

Not quite, what is the significant of -41?

Thanks.

Your code

ListBox1.AddItem .Cells(c.Row, 4).Value

suggested that you were populating data from Column D hence the offset from the search range

Glad you managed to adjust suggestion to meet your requirement

Dave