|
不错!可以改进如下:
Function mlookup(a As String, b As Range, c As Long) As String
Dim arr()
arr = b ' 将数组定义为被查询的数据源区域
Dim i As Long
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
If arr(i, 1) = a Then ' 当数组中第一列的值与查找目标值相同时,
' If arr(i, 1) = a And arr(i, c) <> "" Then ' 如果查询的值忽略空值, 则需要使用此语句
dic.Add i, arr(i, c) ' 将查询需要返回的值添加到字典中的item
End If
Next i
Erase arr() ' 清空数组
Dim vl(), va As String
vl = dic.items ' 将字典中所有item转为数组
Set dic = Nothing ' 清空字典
'For i = 0 To UBound(vl) ' 将数组中的值组合在一起, 注意: 下标从0开始,
' va = va & "," & vl(i)
'Next i
If UBound(vl) = -1 Then
mlookup = "None" ' 无匹配记录的显示结果
Else
mlookup = Join(vl, ",") ' 转化将查询结果
Erase vl ' 清空数组
End If
End Function |
评分
-
1
查看全部评分
-
|