VBA code for checking multiple criteria

  • Hi,


    Am trying to find row number by matching multiple criteria without the use of array function and using the below code.

    This works perfectly in excel (without the worksheetfunction command) but does not work in VBA. Can someone please help with the code


    Range("L5").Value = WorksheetFunction.IfError(WorksheetFunction.Match(1, Application.WorksheetFunction.Index((Range("A1:A10") = "c") * (Range("B1:B10") = "a"), 0, 1), 0), "")

  • Hi and Welcome to the Forum :)


    Please attach your file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks Carim.


    Attached is the file.

    Am trying to get the row number if value in column 'A' = "c" and value in column 'B' = "a" .

    And want to do this with the array formula (ie without the cntrl + shift + enter)

  • Hello,


    Thanks for the file


    Not sure to understand what is your expected final result ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    Expected result is that Cell 'F6' will have value 7.

    This is the row that has value "c" in column 'A' and value "a" in column 'B".


    That is the forumla - Range("F6").Value = ....


    Have highlighted the cells.

  • Hi again,


    Sorry ... but what is for you the difference between Row 3 and Row 7 ...


    Since both rows display the exact same contents : c and a

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi,


    Attached is your test file


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Awesome! This works perfectly Carim.


    I did not find this way to use Match in any of the online docs.

    Any website that you suggest i can refer to.


    Two more questions -

    1) If there are multiple rows that have value "c" and "a" (like in attached sheet), then how do i get all row numbers. Like in Cell F3 get value 7 and in cell F3 get value

    2) If I want to get rows which have either 'c' or 'b' in column 'A' and value 'a' in column 'B' then how do i get those


    I tried using 'OR' instead of '&' in the match formula but that gives and error.


    Thanks Again!

  • Hello,


    Thanks for your Thanks AND for the Like :)


    Attached is your Version 2 for multiple answers ...;)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Pleased to hear you could fix your problem ;)


    Thanks a lot for your Thanks... AND for the Like :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)