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:


    Code
    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):


    Code
    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.

    ['code]


    your code goes between these tags


    ['/code]


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


    Thanks.

Participate now!

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

Register Yourself Login