MATCH formula with two criteria as Worksheet Function (VBA)

user_1744727 0
Viewed 77

Take this worksheet scenario - in the yellow cell I've used an array formula to locate the 5th row in the table: {=MATCH(4&"jkl",A1:A5&B1:B5,0)}

worksheet

I can't for the life of me translate this into a VBA worksheet function. Here's what I thought would be right:

MsgBox WorksheetFunction.Match([4 & "jkl", .Range("A1:A5") & .Range("B1:B5")], 0)

Any advice gratefully received.

worksheet match criteria vba excel
1 Answers

I asked a similar question here but the proposed solution has difficulty handling the concatenated ranges, so:

Taking advantage of the fact that in VBA MATCH() can handle either a Range or an Array:

Sub dural()
    Dim arr(1 To 5, 1 To 1)

    For i = 1 To 5
        arr(i, 1) = Cells(i, 1) & Cells(i, 2)
    Next i

    With Application.WorksheetFunction
        MsgBox .Match("4jlk", arr, 0)
    End With
End Sub

enter image description here

Also, as GSerg commented, we could also use Evaluate():

Sub test2()
    Dim s As String
    s = "=MATCH(4 & ""jkl"",A1:A5 & B1:B5,0)"
    MsgBox Evaluate(s)
End Sub
user_24746560
Related