+ Reply to Thread
Results 1 to 10 of 10

Dictionary instead vlookup function - VBA

  1. #1
    Registered User
    Join Date
    03-02-2021
    Location
    Uruguay
    MS-Off Ver
    2019
    Posts
    7

    Exclamation Dictionary instead vlookup function - VBA

    Can someone explain me who to change my vlookup formula into a dictionary in order to improve speed?


    LastColMyFile = Workbooks(MyFile).Worksheets("Hoja1").Cells(12, Columns.Count).End(xlToLeft).Column
    LastRowBase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    Range(Cells(13, 7), Cells(LastRowBase, LastColMyFile)).Formula = "=+VLOOKUP(RC1,[" & MyFile & "]Hoja1!R13C1:R500C50,+COLUMN([" & MyFile & "]Hoja1!R[1]C),FALSE)"
    Workbooks(MyFile).Close SaveChanges:=False

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Dictionary instead vlookup function - VBA

    If you want dictionary to get the results as VLookUp and spee up, you will need to sore the data in an array first.

    If you upload a workbooks, it will help.

  3. #3
    Registered User
    Join Date
    03-02-2021
    Location
    Uruguay
    MS-Off Ver
    2019
    Posts
    7

    Re: Dictionary instead vlookup function - VBA

    I cant upload the workbooks, because there are many. The macro loops across a file (myfile) that has many workbooks. Inside each workbook the value to lookup is always in column A, and the col_index always starts from cell G7 until the first empty column (the workbooks have different number of columns and rows).

    BASE is the master workbook where the values being "paste". In BASE the number of rows is of 1004.
    Last edited by CamiV22; 03-02-2021 at 01:23 AM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Dictionary instead vlookup function - VBA

    If you say so, wait for someone else.

  5. #5
    Registered User
    Join Date
    03-02-2021
    Location
    Uruguay
    MS-Off Ver
    2019
    Posts
    7

    Re: Dictionary instead vlookup function - VBA

    It does not allow me to upload files

  6. #6
    Registered User
    Join Date
    03-02-2021
    Location
    Uruguay
    MS-Off Ver
    2019
    Posts
    7

    Post Re: Dictionary instead vlookup function - VBA

    Does this help ?

    Master_worksheet_BASE.png

  7. #7
    Registered User
    Join Date
    03-02-2021
    Location
    Uruguay
    MS-Off Ver
    2019
    Posts
    7

    Re: Dictionary instead vlookup function - VBA

    The image above is the "BASE"and this is a myfile example

    Attachment 721337

  8. #8
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Dictionary instead vlookup function - VBA

    If you want to use a Scripting Dictionary object for better performance, you'd realize even better performance by SORTING your lookup table on its 1st column in ascending order.

    Since your lookup table is in another workbook, next best would be copying the VALUES of '[MyFile]Hoja'!A13:A500 into a range somewhere in ThisWorkbook, use the column immediately to the right of it to store sequential integers from 1 to 488, sort that 2-column range on its 1st column in ascending order, then use range matches in that range's 1st column to get the row indices in that range's 2nd column for the range in MyFile. That is, with that other range set as the VBA variable xrng, it'd work something like

    Please Login or Register  to view this content.
    On the other hand, if you're closing MyFile and you want formulas then referring to a range in a closed workbook, external references into close workbooks are ALWAYS slow.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Dictionary instead vlookup function - VBA

    Quote Originally Posted by CamiV22 View Post
    It does not allow me to upload files
    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

  10. #10
    Registered User
    Join Date
    03-02-2021
    Location
    Uruguay
    MS-Off Ver
    2019
    Posts
    7

    Re: Dictionary instead vlookup function - VBA

    Ive nver use the index formula on vba. Can you explian mi a little bit more this: "..somewhere in ThisWorkbook, use the column immediately to the right of it to store sequential integers from 1 to 488, sort that 2-column range on its 1st column in ascending order, then use range matches in that range's 1st column to get the row indices in that range's 2nd column for the range in MyFile. That is, with that other range set as the VBA variable xrng"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel Function Dictionary
    By jomili in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-28-2019, 09:41 AM
  2. Scripting Dictionary equivalent to Vlookup
    By sriley5 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2018, 05:16 PM
  3. Vlookup with arrays and dictionary
    By kasan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2018, 03:03 PM
  4. Speed up vlookup with Scripting.Dictionary
    By dondada82 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-19-2017, 04:06 PM
  5. Scripting Dictionary (working as vlookup) and add calculated columns
    By mumsys in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-23-2016, 04:49 AM
  6. [SOLVED] Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Function
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-12-2016, 08:28 AM
  7. Dictionary - Using a dictionary of dictionaries to hold individual orders
    By wazimu13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2015, 08:32 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1