Adding Items to ComboBox that Match a Value

Gerald Lariosa 0
Viewed 29

How to add items to 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 looks 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.

vba excel
1 Answers

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
Christofer Weber0
Related