具有多个条件的Vlookup，返回的数组插入到ComboBox中

Question

是否有一种方法可以使用VBA与多个条件进行Vlookup，然后将这些多个返回值插入到组合框中?下面这张桌子，我想要"马林鱼"。作为搜索条件，然后将{RBI, Score, ABV}作为选项插入到ComboBox中。这可能吗?

示例数据:

<表类> 标识符值 tbody><<tr>海盗得分马林鱼RBI马林鱼得分马林鱼含量海盗小时

填充ComboBox

一个简单的例子

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

如果您处理了vers的新动态数组特性。MS365,您可以从以下函数filtered()

中获益

• (a)返回求值的Filter()公式字符串(基于帮助函数MakeFormula和
的结果)
• (b)检查可能的非发现

Function filtered(data As Range, Optional ByVal criteria As String = "Marlins")
'a) Return filtered data as 1-based 2D array.
filtered = data.Parent.Evaluate(MakeFormula(data, criteria))
'b) (Provide for possible non-findings via Error check).
If IsError(filtered) Then filtered = Array(Empty)
End Function

帮助功能MakeFormula

Function MakeFormula(data, ByVal criteria As String) As String
'0. Get Column addresses.
Dim critAddr$: critAddr = data.Columns("A").Address(0, 0)
Dim valsAddr$: valsAddr = data.Columns("B").Address(0, 0)
'1. Return concatenated formula - e.g. "FILTER(B2:B6,A2:A6=""Marlins"")"
MakeFormula = "FILTER(" & valsAddr & "," & critAddr & "=""" & criteria & """)"
End Function

示例调用

将整个数据范围作为参数(和/或可选标准，例如"Marlins")传递就足够了。

注意，您不需要将组合框的值作为整个.List属性清除获取(重新)由筛选的值分配。

Sub ExampleCall
' Reference the worksheet.
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Metadata")
' Assign the filtered data to the combo box´es .List property.
With ws.OLEObjects("ComboBox1")
.Object.list = filtered(ws.Range("A2:B6"))
End With
End Sub

使用ActiveX组合框，并让默认名称为ComboBox1。

Sub test()
Dim rg As Range: Dim cell As Range: dim crit as string
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

代码假设您的数据与图像附件中的数据完全相同。
因此，标识符标头在单元格A1中，它的数据从单元格A2开始。
Value头在B1单元格中，它的数据从B2单元格开始。

进程:
它为标识符数据创建一个范围，作为变量rg。

用value "Marlins"创建变量crit。

然后在rg中，它替换所有包含文本" mar林斯"使用逻辑TRUE，获取包含"TRUE"的单元格范围。offset(0,1)，执行循环将每个循环单元格的值作为ComboBox1项，将TRUE值重新设置为"Marlins"

子程序必须在数据表有效的地方运行。