excel vba - 多条件索引匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27790846/
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
excel vba - multiple criteria index match
提问by xndr
I'm trying to do a multiple criteria index match function in vba but I can't seem to get the results. The code I used is the following:
我正在尝试在 vba 中执行多条件索引匹配功能，但似乎无法获得结果。我使用的代码如下：
wsDest.Range(wsDest.Cells(i, X), wsDest.Cells(i, X)) = _
Application.WorksheetFunction.Index(wsSour.Range("C3:C8763"), _
Application.WorksheetFunction.Match(wsDest.Cells(i, 1) & "&" & wsDest.Cells(i, 2), _
wsSour.Range("A3:A8763") & "&" & wsSour.Range("B3:B8763"), 0), 0)
For the match portion I was trying to use excel's method of
对于匹配部分，我试图使用 excel 的方法
=MATCH(criteria1 & criteria2,range1 & range2,0)
=MATCH(criteria1 & criteria2,range1 & range2,0)
回答by Tim Williams
You can use the WorkSheet.Evaluatemethod to do this.
您可以使用该WorkSheet.Evaluate方法来执行此操作。
Here's a simple example:
这是一个简单的例子：
Sub Tester()
Dim v, sht, a1, a2
Set sht = ActiveSheet
a1 = sht.Cells(7, 1).Address(False, False)
a2 = sht.Cells(7, 2).Address(False, False)
v = sht.Evaluate("MATCH(" & a1 & "&" & a2 & ",A2:A5&B2:B5,0)")
sht.Range("B9") = v
End Sub
EDIT: here's a more robust example taking into account the different sheets
编辑：这是一个更强大的例子，考虑到不同的工作表
Sub Tester2()
Dim v, shtDest, shtSrc, a1, a2, i
Set shtDest = ThisWorkbook.Sheets("Dest")
Set shtSrc = ThisWorkbook.Sheets("Source")
i = 1
a1 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 1).Address(False, False)
a2 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 2).Address(False, False)
Debug.Print a1, a2
v = shtSrc.Evaluate("MATCH(" & a1 & "&" & a2 & ",A2:A9&B2:B9,0)")
If Not IsError(v) Then
shtDest.Cells(i, 3).Value = shtSrc.Range("C2:C9").Cells(v).Value
End If
End Sub
