Issue

This Content is from Stack Overflow. Question asked by Gerald Lariosa

How to add items in ComboBox that match a value?

This is my code:

Dim v2, e2
With Sheets("Item").Range("A2:A100")
    v2 = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e2 In v2
        If Not .exists(e2) Then .Add e2, Nothing
    Next
    If .Count Then Me.cmbItemDescription.List = Application.Transpose(.keys)
End With

My Table look like this:

Table

Output must be:

ComboBox Output

The values ‘GARBAGE BAG, large, 100s’ and ‘GARBAGE BAG, XL, 100’s are tagged as ‘Available’ according to the table so it should be listed in the Combobox.

Thank you in advance.

Solution

The way it is now, we have no idea what is happening in the C column, since we only have an array of column A. So when we add items to the list, we can’t really use v2 to get this information, unless we want to search the sheet each time, which doesn’t sound like a good idea.

One easy way to go about this, would be to either create a second array for column C, or include column C in the existing array.

Then use a different kind of loop to use both values in the new array(s).
Probably easier to use a single array, but you can use the exact same approach with two arrays, as long as they have the same size.

Dim v2() As Variant, e2 As Integer
v2 = Sheets("Item").Range("A2:C100").Value
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For e2 = 1 To UBound(v2)
        If UCase(v2(e2, 3)) = "YES" Then
            If Not .exists(v2(e2, 1)) Then .Add v2(e2, 1), Nothing
        End If
    Next e2
    If .Count Then Me.cmbItemDescription.List = Application.Transpose(.keys)
End With

This Question was asked in StackOverflow by Gerald Lariosa and Answered by Christofer Weber It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.