0

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
HelpMe
  • 7
  • 3
  • If you [construct a `Dictionary`](https://excelmacromastery.com/vba-dictionary/) of the languages in VBA, your lookup would be extremely fast. Can you guarantee that the "beginning" of a profile in Tab B/Column D contains the language text to be found in Tab A/Column A? Also, how to [avoid using `Select`](https://stackoverflow.com/a/10717999/4717755). – PeterT Sep 30 '22 at 14:48

1 Answers1

0

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

cedrcr4
  • 43
  • 3