VBA / INDEX MATCH - Partial data-CodePudding

my macro takes a long time to proceed and sometimes Excel blocks.

Tab A: list of specific languages in the "Database" tab (column A ).

Tab B: list of some virtual profiles (column D) including some beginning with a specific language "French ....".

What I need to do: mention in column 14 from Tab B these specific languages (if they exist).

I used below macro but it took sometimes up to 2mn when it works. Do you know what I have to change? Thanks

    Worksheets("MyFile").Activate

    Range("R2").Select
    ActiveCell.Formula2R1C1 = _
        "=INDEX(Database!C[-17],MATCH(1,INDEX(COUNTIF(RC[-14],""*""&Database!C[-17]&""*""),),0))&"""""
    Range("R2").Select

CodePudding user response：

This formula is very long because it processes ALL the rows up to 1048576 (it basically treats the formula as an array / CSE formula because of COUNTIF(), which returns an array containing 1048576 values, in each cell the formula is calculated!). You need not to use an entire column reference. There is also an extra INDEX() that I removed. And you also need to filter out possible empty cells in the column containing the virtual profiles, that's why I am looking up for 2 in the MATCH(), not 1 anymore:

Range("R2").Select
ActiveCell.Formula2R1C1 = _
    "=IFERROR(INDEX(Database!C[-17],MATCH(2,(Database!R1C1:R1000C1<>"""") COUNTIF(RC[-14],""*""&Database!R1C1:R1000C1&""*""),0))&"""",0)"

Note: you speak of column 14 in you tab/sheet named "MyFile", but that's column N not R