在 Excel VBA 中连接多个 MATCH 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24787010/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Concat multiple MATCH criteria in Excel VBA
提问by jkayani
So in Excel, we know it's possible to test against multiple criteria via concatenation, like this:
因此，在 Excel 中，我们知道可以通过串联对多个条件进行测试，如下所示：
MATCH(criteria1&criteria2, Range(), 0)
where criteria1and criteria2are 2 separate criteria.
其中criteria1和criteria2是 2 个独立的标准。
I'm trying to automate this thing in Excel VBA, like this:
我正在尝试在 Excel VBA 中自动执行此操作，如下所示：
WorksheetFunction.Match(criteria1 + "&" + criteria2, Range(), 0)
My question is, how do I replicate the same concatenation of criteria with the ampersand, in VBA form? In the version above, Excel keeps telling me it can't use the Match function of the WorkSheetFunction class, which I'm attributing to the failed concatenation attempt above. Any suggestions or advice would be greatly appreciated.
我的问题是，如何以 VBA 形式复制与符号相同的标准串联？在上面的版本中，Excel 一直告诉我它不能使用 WorkSheetFunction 类的 Match 函数，我将其归因于上面的连接尝试失败。任何建议或意见，将不胜感激。
Oh, and here's a link to a Microsoft Knowledge Base article about using multiple criteria in MATCH(): http://support.microsoft.com/kb/59482
哦，这里有一个关于使用多个条件的 Microsoft 知识库文章的链接MATCH()：http: //support.microsoft.com/kb/59482
EDIT: I realized I wasn't putting 2 ranges to correspond with my 2 criteria. The problem is I don't know how to concatenate 2 ranges, because mine are in the form:
编辑：我意识到我没有将 2 个范围与我的 2 个标准相对应。问题是我不知道如何连接 2 个范围，因为我的是以下形式：
Range(Cells(1,columnIndex),Cells(rowCount,columnIndex))
as opposed to A1:A200. Any ideas on how to convert, or to concat the ranges in their current form?
与A1:A200. 关于如何转换或以当前形式连接范围的任何想法？
回答by Gary's Student
This works:
这有效：
Sub dural()
crit1 = "find "
crit2 = "happiness"
N = Application.WorksheetFunction.Match(crit1 & crit2, Range("A1:A10"), 0)
MsgBox N
End Sub
with, say A3containing:
与，说A3包含：
find happiness
找到幸福
EDIT#1:
编辑#1：
Consider the case of multiple criteria in several columns. For example:
考虑多列中有多个条件的情况。例如：
and we want VBAto retrieve the name of a small, black, dog
我们希望VBA检索小黑狗的名字
without VBAin a worksheet cell we can use:
在工作表单元格中没有VBA，我们可以使用：
=INDEX(D1:D100,SUMPRODUCT(--(A1:A100="dog")*(B1:B100="small")*(C1:C100="black")*ROW(1:100)),1)
to get the name Oscar
获得奥斯卡这个名字
we can use the same formula in VBA
我们可以在VBA 中使用相同的公式
Sub luxation()
Dim s As String, s2 As String
s = "INDEX(D1:D100,SUMPRODUCT(--(A1:A100=""dog"")*(B1:B100=""small"")*(C1:C100=""black"")*ROW(1:100)),1)"
s2 = Evaluate(s)
MsgBox s2
End Sub
回答by Tim Williams
Doesn't readily map to a VBA implementation, but you can cheat a bit using Evaluate:
不容易映射到 VBA 实现，但您可以使用 Evaluate 作弊：
Sub Tester()
Dim v1, v2, f
v1 = "y"
v2 = 11
Sheet1.Names.Add Name:="X", RefersTo:=v1
Sheet1.Names.Add Name:="Y", RefersTo:=v2
f = "MATCH(X&Y,$A:$A&$B:$B, 0)"
Debug.Print Sheet1.Evaluate(f)
End Sub
or skipping the names:
或跳过名称：
Sub Tester2()
Const F_TEMPL As String = "MATCH(""{V1}""&""{V2}"",$A:$A&$B:$B, 0)"
Dim v1, v2, f
f = Replace(F_TEMPL, "{V1}", "x")
f = Replace(f, "{V2}", 12)
Debug.Print Sheet1.Evaluate(f)
End Sub
回答by Bmo
You still need to send the MATCHargument body as a string. The '+' does not concatenate.
您仍然需要将MATCH参数主体作为字符串发送。'+' 不连接。
WorksheetFunction.Match(criteria1 & "&" & criteria2, Range(), 0)
Should concatenate the two values and execute the match.
应该连接两个值并执行匹配。
