Posts by TheShakingHand

    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
    1. 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
    1. 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?