Excel VBA-多个条件索引匹配 [英] excel vba - multiple criteria index match

问题描述

我正在尝试在vba中执行多条件索引匹配功能，但似乎无法获得结果. 我使用的代码如下:

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:

  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)

对于匹配部分，我试图使用excel的

For the match portion I was trying to use excel's method of

=MATCH(criteria1 & criteria2,range1 & range2,0)

=MATCH(criteria1 & criteria2,range1 & range2,0)

推荐答案

您可以使用WorkSheet.Evaluate方法执行此操作.

You can use the WorkSheet.Evaluate method to do this.

这是一个简单的例子:

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

这是一个考虑到不同工作表的更可靠的示例

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

这篇关于Excel VBA-多个条件索引匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！