Creating a matrix formula with Index, Match in VBA

  • Hello,

    I am trying to assign a Matrix formula with Index and Match functions to a variable in VBA. I know it is possible to assign matrix formulas to cells, but that is not what I want per se. I want it in a variable in VBA where I use it, not in a cell.

    The function below works like I expect it to:

    BBb = Application.WorksheetFunction.Index(Sheets("Parameters - DUMMY_ALL_BLOCKS").Range("H:H"), Application.WorksheetFunction.Match("CLA", Sheets("Parameters - DUMMY_ALL_BLOCKS").Range("A:A"), 0))

    But I want multiple search criteria so I tried (for starters with 1 criterium):

    BBb = Application.WorksheetFunction.Index(Sheets("Parameters - DUMMY_ALL_BLOCKS").Range("H:H"), Application.WorksheetFunction.Match(1, ("CLA" = Sheets("Parameters - DUMMY_ALL_BLOCKS").Range("A:A")), 0))

    This fails on a type mismatch. Apparently it can't handle: "CLA" = Sheets("Parameters - DUMMY_ALL_BLOCKS").Range("A:A")

    But this definately works in a regular excel cell. Does anybody know how to solve this?

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.


    your code goes between these tags


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!