当我从Excel (在单元格中)调用函数时:
=allVlookup(O24,A:D,3,"")
vs通过vba
MsgBox allVlookup(Range("O24"), Range("A:D"), 3, "")
我得到了不同的结果。当从Excel中调用时,我只得到第一个匹配项,但当从具有相同参数的vba测试子中调用时(除了将Range
添加到参数以允许该子对象运行之外),我会得到完整的结果(不止一个)。
我使用的函数是:
Public Function allVlookup(lookupRange As Range, tableRange As Range, colIndex As Integer, Optional delimiter As String = "") As String
Dim c As Range
Dim firstAddress As String
'MsgBox tableRange.Address ' this is correct
'With Sheets(4).Range("A1:C12").Columns(1)
'With Range("A1:C12").Columns(1)
'this doesn't allow things to work right either (???)
'Set tableRange = Range("A:D")
'Set lookupRange = Range("O24")
'search only the first column for matches
With tableRange.Columns(1)
Set c = .Find(what:=lookupRange.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'add the delimiter
If (allVlookup <> "") Then
allVlookup = allVlookup + delimiter
End If
'append value to previous value
allVlookup = allVlookup + c.Offset(0, colIndex).Value
Set c = .FindNext(c)
'exit conditions
'no match found
If (c Is Nothing) Then
Exit Do
'we're back to start
ElseIf (c.Address = firstAddress) Then
Exit Do
End If
Loop
End If
End With
End Function
我无法解释为什么会发生这种情况。
我可以做些什么来使输出相同呢?
发布于 2013-03-29 02:02:46
将.Find
行更改为:
Set c = .Find(what:=lookupRange.Value2, after:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)
并另外将.FindNext
更改为:
Set c = .Find(what:=lookupRange.Value2, after:=c, LookIn:=xlValues, LookAt:=xlWhole)
还请记住,tableRange
范围应该有列标题。如果不是,结果顺序将不会是预期的。
最后一句的附加(编辑)解释。如果您有这种类型的表:
| A | B | C | D |
--+-----+-----+-----+-----+
1 | ABC 1 2 A
2 | ABC 3 4 B
3 | ABC 5 6 C
当在range("A1:D3")
中搜索ABC以从D列获取数据时,您将得到如下结果:BCD
。要获取ABC
,第一行中应该有列标题。
https://stackoverflow.com/questions/15688360
复制相似问题