CODEWITHSUNDEEP
arraysexcelvbamatchworksheet-function
Simone Evans
Simone Evans

Reputation: 311

Excel VBA worksheetfunction Index Match with an Array

Is there a way to get VBA to except Array formulas with INDEX MATCH when using a .worksheetfunction?

My first formula works since it's not an array I presume?

This code works

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

But then when I add a 2nd match I get an arror: Type mismatch

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)

Both sheetname and wsMaster.name are string. The wsMaster.Name also gets the correct sheetname. So it must be the array?

Upvotes: 0

Views: 2262

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

If I'm understanding correctly that you want to do a "two column match" as here: 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

Upvotes: 2

Related Questions