Concat多个MATCH标准在Excel VBA中 [英] Concat multiple MATCH criteria in Excel VBA

问题描述

所以在Excel中，我们知道可以通过连接来测试多个条件，如下所示：

So in Excel, we know it's possible to test against multiple criteria via concatenation, like this:

MATCH(criteria1&criteria2, Range(), 0)

其中 criteria1 和 criteria2 是两个独立的标准。

where criteria1 and criteria2 are 2 separate criteria.

我试图在Excel VBA中自动化这个东西，比如这是：

I'm trying to automate this thing in Excel VBA, like this:

WorksheetFunction.Match(criteria1 + "&" + criteria2, Range(), 0)

我的问题是，如何以VBA形式复制与&符号相同的条件级联？在上面的版本中，Excel不断告诉我，它不能使用WorkSheetFunction类的Match函数，我归因于上面的失败的级联尝试。任何建议或建议将不胜感激。

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.

哦，这里是一个Microsoft知识库文章的链接，关于在 MATCH（）中使用多个条件： http://support.microsoft.com/kb/59482

Oh, and here's a link to a Microsoft Knowledge Base article about using multiple criteria in MATCH(): http://support.microsoft.com/kb/59482

编辑：我意识到我没有把2个范围与我的2个标准相符。问题是我不知道如何连接2个范围，因为我的形式是：

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:

Range(Cells(1,columnIndex),Cells(rowCount,columnIndex))

而不是 A1 ：A200 。任何关于如何转换的想法，还是以目前形式的范围？

as opposed to A1:A200. Any ideas on how to convert, or to concat the ranges in their current form?

推荐答案

不容易映射到VBA执行，但是您可以使用Evaluate进行欺骗：

Doesn't readily map to a VBA implementation, but you can cheat a bit using 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$2:$A$5&$B$2:$B$5, 0)"

    Debug.Print Sheet1.Evaluate(f)

End Sub

或跳过名称：

Sub Tester2()

    Const F_TEMPL As String = "MATCH(""{V1}""&""{V2}"",$A$2:$A$5&$B$2:$B$5, 0)"
    Dim v1, v2, f

    f = Replace(F_TEMPL, "{V1}", "x")
    f = Replace(f, "{V2}", 12)

    Debug.Print Sheet1.Evaluate(f)

End Sub

这篇关于Concat多个MATCH标准在Excel VBA中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！