0

I would like to use vlookup to realize a search for 24k lines. The source data is not that much, but my vlookup runs so slowly in vba. To solve this problem, I tried index and array. Index is a little bit better but array should be much better performed. However, when I launch the code, the array function runs even more slowly than before.

Here is my code:

vlookup -

Workbooks(currentfilename).Sheets("ID").Activate
row = Workbooks(currentfilename).Sheets("ID").Range("A"& Rows.Count).End(xlUp).Row

For j=4 To row

Range("E"&j) = Application.WorksheetFunction.Vlookup(Range"C"&j, Workbooks(Feuil1).Sheets("limit").Range("A:S"),19,0)

Next j

array -

Dim Inarray as Variant
Dim Col_Data as Variant
Dim col_ar as Variant

Inarray = Workbooks(Feuil1).Sheets("limit").Range("A1:S55000")
col_ar = Workbooks(Feuil1).Sheets("limit").Range("A1:A55000")
Workbooks(Feuil1).Close False

Workbooks(currentfilename).Sheets("ID").Activate
row = Workbooks(currentfilename).Sheets("ID").Range("A"& Rows.Count).End(xlUp).Row

Col_Data = Application.Index(Workbooks(currentfilename).Sheets("ID").Range(a1:C5000").Value,,3)

For j=4 To row

Workbooks(currentfilename).Sheets("ID").Range("E"&j).Value = WorksheetFunction.Index(Inarray, WorksheetFunction.Match(Col_Data(j,1),col_ar,0,19)

Workbooks(currentfilename).Sheets("ID").Range("F"&j).Value = Range("D"&j) / Range("E"&j)

Next j

Thank you a lot for your help

FunThomas
  • 23,043
  • 3
  • 18
  • 34
Huo Anna
  • 9
  • 1
  • 2
    If you code is working, but you just want it to work faster, then your question belongs in Code Review instead - https://codereview.stackexchange.com/ – braX Sep 21 '20 at 10:39
  • If you have 24k rows of data, why are you are you setting your array to `S55000`? Some pointers: Don't use things like `Activate` (why.. [this is why](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)). In your `Vlookup` code, you are setting the range as `"A:S"`. Try setting it to your last row: `"A" & row & ":S" & row`. Also, it is highly recommended that you don't use reserved words as variable name (**`row`**. you could change that to something like: **`oRow`**) – Zac Sep 21 '20 at 10:52
  • Also, built in functionality will always run faster than any code you write (`Vlookup` vs `Array`) – Zac Sep 21 '20 at 10:55
  • Match works much faster if you leave the data on the worksheet - it's optimized for that use. If you run match against an erray in VBA it will be *much* slower. – Tim Williams Sep 21 '20 at 15:38

0 Answers0