多条件查询并返回多列数据,是在数据查询时经常会使用的功能,利用字典与数组结合,能够高效地实现此功能。数据源图如下。
现需要根据款号,编号两个条件,查询工艺、数量、报价信息,示例代码如下。
Function strDicLook(ByVal rngRegion As Range _
, ByVal rngConditionO As Range _
, ByVal rngConditionT As Range _
, ByVal intColumn As Integer)AsString
Dim dicData As Object, avntList() As Variant
Dim i As Integer
Set dicData = CreateObject("scripting.dictionary")
avntList = Intersect(rngRegion, rngRegion.Parent.UsedRange).Value
For i = 1 To UBound(avntList)
dicData(avntList(i, 1) & avntList(i, 2)) = Array(avntList(i, 1), avntList(i, 2), avntList(i, 3), avntList(i, 4), avntList(i, 5))
Next i
strDicLook = dicData(rngConditionO & rngConditionT)(intColumn - 1)
End Function</