时间戳

首页 > 解决方案 > Excel VBA 工作表函数索引与数组匹配

arrays - Excel VBA 工作表函数索引与数组匹配

问题描述

使用 .worksheetfunction 时，有没有办法让 VBA 使用 INDEX MATCH 排除数组公式？

我的第一个公式有效，因为它不是我认为的数组？

此代码有效

Dim VType As string
VType = Application.WorksheetFunction.Index(Sheets(sheetname).Range("$B:$B"), Application.WorksheetFunction.Match("*" & VendorCode & "*", Sheets(sheetname).Range("$A:$A"), 0), 1)

但是当我添加第二个匹配时，我得到一个错误：类型不匹配

Dim RetORWaste As String
RetORWaste = Application.WorksheetFunction.Index(Sheets(wsMaster.Name).Range("$F:$F"), Application.WorksheetFunction.Match(("*" & VendorCode & "*") & ("*" & VRegion & "*"), (Sheets(wsMaster.Name).Range("$B:$B")) & (Sheets(wsMaster.Name).Range("$C:$C")), 0), 1)

sheetname 和 wsMaster.name 都是字符串。wsMaster.Name 也获得正确的工作表名称。所以一定是数组？

标签： arraysexcelvbamatchworksheet-function

解决方案

如果我正确理解您想要进行“两列匹配”，如下所示： https ://www.excel-easy.com/examples/two-column-lookup.html

Sub tester()

    Dim RetORWaste As String, wsMaster As Worksheet, m, f
    Dim VendorCode, VRegion

    Set wsMaster = ActiveSheet

    VendorCode = "A"
    VRegion = "B"

    f = "=MATCH(""*{vend}*""&""*{region}*"",B:B&C:C,0)"
    f = Replace(f, "{vend}", VendorCode)
    f = Replace(f, "{region}", VRegion)

    m = wsMaster.Evaluate(f) '<<do not use Application.Evaluate here, or the
                             '  formula will evaluate in the context of the 
                             '  active sheet, which might not be wsMaster

    If Not IsError(m) Then
        'got a match so get the value from col F
        RetORWaste = wsMaster.Cells(m, "F")
        Debug.Print RetORWaste
    End If

End Sub

推荐阅读