How to write array formula in VBA syntax (not Excel-Formula)

Question

To do lookup with two criteria, in Excel-Formula, it would be written as:

{MATCH(1, 1*(A1:A5=100)*(B1:B5=150), 0)}

How do i write the above formula in VBA syntax for the WorksheetFunction.Match function?

Gary's Student · Accepted Answer · 2016-03-31 13:05:50Z

3

Consider:

Sub dural()
    Dim m As Long

    With Application.WorksheetFunction
        m = .Match(1, [1*(A1:A5=100)*(B1:B5=150)], 0)
    End With

    MsgBox m
End Sub

This shows both the array formula in the worksheet and the VBA equivalent.

answered Mar 31, 2016 at 13:05

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rosetta Over a year ago

Amazing! Thanks so much!

msinfo Over a year ago

@Gary'sStudent How to replace, 100 and 150 using variables? I am getting error when I fill those value with variables. Seems not doing concatenation with & right.

Stevetb77 · Accepted Answer · 2019-03-08 19:21:09Z

2

I don't have enough rep to post a comment.. This is to aid anyone searching for how to use the answer from "Gary's Student" with variables. I wrestled with this for over an hour. The brackets [] used are a shortcut for evaluate(string) You have to replace those and wrap them in the evaluate method. Since the parameter is a string, you can construct the string yourself with the variables you need.

Sub dural()
Dim m As Long

With Application.WorksheetFunction
    m = .Match(1, evaluate("1*(A1:A5=100)*(B1:B5=150)"), 0)
End With

MsgBox m

End Sub

answered Mar 8, 2019 at 19:21

Stevetb77

611 bronze badge

1 Comment

Shawn V. Wilson Over a year ago

This is too informative to be a comment! (Not sure what the "evaluate method" means, though...)

Collectives™ on Stack Overflow

How to write array formula in VBA syntax (not Excel-Formula)

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related