excel自带的vlookup函数是不支持返回多个值的,于是我自己写了个lvlookup(value, rnglookup as range, colArr() as integer, optional accu as byte = 1)函数,支持在rnglookup中精确或模糊查找value,并根据colArr()中指定的列返回匹配结果。
现在在sub中调用该函数,可以正确传参并给出模糊匹配结果,但在excel中,不知道第三个参数该怎么写
Function lvlookup(value, rngLookup As Range, colArr() As Integer, Optional accu As Byte = 1)
Dim intLp As Integer, lenth%, i%, j%, col, counter%
counter = 0
Dim reArr() '定义一个resultArray用来存储匹配到的结果
lenth = rngLookup.Columns.Count
ReDim reArr(1 To lenth) '因为不知道到底会匹配几个结果,就先给一个足够大的长度,最后再redim一下
If accu = 1 Then '默认accuracy =1 , 模糊查找
For i = 1 To rngLookup.Rows.Count
If value >= rngLookup(1)(i) And value <= rngLookup(1)(i + 1) Then
For Each col In colArr()
counter = counter + 1
reArr(counter) = rngLookup(col)(i)
Next col
ReDim Preserve reArr(1 To counter) '裁掉多余的长度
lvlookup = reArr()
End If
Next i
End If
End Function
在sub中调用函数:
Sub test1()
Dim arr(), colArr(1 To 2) As Integer
colArr(1) = 3
colArr(2) = 4
arr() = lvlookup(11000, Range("B1:E8"), colArr())
For Each varlp In arr()
MsgBox varlp
Next varlp
End Sub
其中range("B1:E8")是这个表里的:
=lvlookup(Sheet3!I2,税表!B1:E8,array(3,4))
按ctrl+shift+enter结果为#VALUE,附注内容为:公式中所用的某个值是错误的数据类型
在excel里写成:
=lvlookup(Sheet3!I2,税表!B1:E8,{3,4})
结果一样
请问能否在不改代码的前提下,在excel里正确调用该函数?