Is there a way to use VBA to Vlookup with multiple criteria and then have those multiple returned values then inserted into a ComboBox? For the table below, I would like to have "Marlins" as the search criteria, and then have {RBI, Score, ABV} inserted into a ComboBox as options. Is this possible?

Example Data:

Application.WorksheetFunction.VLookup("Marlins", Worksheets("Metadata").Range("A2:B5"), 2, False)

CodePudding user response：

Populate a ComboBox

A Simple Example

Sub PopulateComboBox()
    
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Metadata")
    
    ' Write the values of the range to a 2D one-based array.
    Dim sData As Variant: sData = ws.Range("A2:B5").Value
    
    ' Write the unique values from column 2, where column 1 is "Marlin",
    ' to the keys of a dictionary.
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' case-insensitive
    Dim r As Long
    For r = 1 To UBound(sData, 1)
        If StrComp(CStr(sData(r, 1)), "Marlin", vbTextCompare) = 0 Then
           dict(sData(r, 2)) = Empty
        End If
    Next r
    
    ' Write the values from the keys of the dictionary to the combo box.
    With ws.ComboBox1 ' active-x combobox on the worksheet
        .Clear
        If dict.Count > 0 Then .List = dict.Keys
    End With
        
End Sub

CodePudding user response：

Use the ActiveX ComboBox, and let the default name as ComboBox1.

Sub test()
Dim rg As Range: Dim cell As Range

    With ActiveSheet
        Set rg = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        .ComboBox1.Clear
    End With

crit = "Marlins"

    With rg
        .Replace crit, True, xlWhole, , False, , False, False
             For Each cell In .SpecialCells(xlConstants, xlLogical).Offset(0, 1)
                ActiveSheet.ComboBox1.AddItem cell.Value
             Next
        .Replace True, crit, xlWhole, , False, , False, False
    End With
    
End Sub

The code assumed that your data exactly the same as in your image attachment.
So, the Identifier header is in cell A1 where it's data starts from cell A2.
And the Value header is in cell B1 where it's data starts from cell B2.

The process:
It make a range for the Identifier data as variable rg.

create variable crit with value "Marlins".

Then within the rg, it replace all rows which contains text "Marlins" with logical TRUE, get the range of the cell which contains "TRUE" offset(0,1), do the loop to put each looped cell value as the ComboBox1 item, bring the TRUE value back to "Marlins".

The sub must be run where the sheet of your data is active.