+ Reply to Thread
Results 1 to 13 of 13

V Lookup Loop taking ages to run

  1. #1
    Registered User
    Join Date
    06-23-2020
    Location
    India
    MS-Off Ver
    2010
    Posts
    25

    V Lookup Loop taking ages to run

    Hi,

    I've been trying to run a code which has data for approx. 35,000 rows and there is a v-lookup loop which is giving the correct results. But the challenge is, this specific module takes more than an hour (1.5 hrs) to run. Is there a way to make the code more efficient?

    What I need: Col. N gets updated in CMDB_Raw Data sheet.

    How: Col. A of Raw Data sheet looked up in sheet- "Master List Asset Categorization" and a matching value from Col. B is populated in Col. N of raw data sheet

    Attaching the file. Also, here is my code:

    [CODE]Sub Categorization()

    Dim lastRow As Long, k As Long, wbk As Workbook, main_wbk As Workbook, lastrow1 As Long, range As range
    Dim main_sht As Worksheet, Sheet1 As Worksheet, sht1 As Worksheet, sepr As String

    Set main_wbk = Workbooks("CMDB_Production_File.xlsb")
    Set main_sht = main_wbk.Sheets("CMDB - Raw Data")

    main_wbk.Activate

    lastRow = main_wbk.Sheets("Master List Asset Categorzation").range("a1").End(xlDown).Row

    k = 2

    Do While main_sht.Cells(k, 1).Value <> ""

    On Error Resume Next

    main_sht.Cells(k, 14).Value = WorksheetFunction.VLookup(main_sht.Cells(k, 8).Value, _
    Sheets("Master List Asset Categorzation").range("a1:b" & lastRow), 2, 0)

    k = k + 1

    Loop

    MsgBox ("Press Categorization button")

    End Sub
    [CODE]
    Attached Files Attached Files
    Last edited by ruchikasharma9727; 06-24-2020 at 04:11 AM. Reason: Adding file and code

  2. #2
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    737

    Re: V Lookup Loop taking ages to run

    Probably yes, there are ways to make it more efficient but without more information or sample file you won't get much help. Refer to the top yellow banner on how to post a sample file - Make the sample file with the exact file format so you don't have to hassle trying to amend the provided code

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,067

    Re: V Lookup Loop taking ages to run

    I have a feeling that there are other issues causing this. I just made a list 45 000 long and my vlookup took a second.

    Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Arrow Re: V Lookup Loop taking ages to run


    Hi,

    this kind of issue often comes from using a 'cell-by-cell' loop

  5. #5
    Registered User
    Join Date
    06-23-2020
    Location
    India
    MS-Off Ver
    2010
    Posts
    25

    Re: V Lookup Loop taking ages to run

    Thank you, I've amended the post with the sample file and my code. Please assist.

  6. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,279

    Re: V Lookup Loop taking ages to run

    Just explain in a concise manner what you want to achieve. Your attachment does not tell us much.
    But before anything, at the top click on "Forum Rules" and read #2.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,796

    Re: V Lookup Loop taking ages to run

    Try
    Please Login or Register  to view this content.
    Edit:Code
    Last edited by jindon; 06-24-2020 at 01:51 AM.

  8. #8
    Registered User
    Join Date
    06-23-2020
    Location
    India
    MS-Off Ver
    2010
    Posts
    25

    Re: V Lookup Loop taking ages to run

    Hi,

    Thank you for sharing the code. I'm trying to use it in my file but fail to understand. I've now attached my file. Could you please assist in it?

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,796

    Re: V Lookup Loop taking ages to run

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-23-2020
    Location
    India
    MS-Off Ver
    2010
    Posts
    25

    Re: V Lookup Loop taking ages to run

    Hi,

    I have been trying to reply to you but this site was down for long. I tried the code but it is still not working.
    I'm attaching the picture of what I get. Below is the code, I have made few tweaks as per requirement.
    For e.g. Instead of Col. O, I used Col. N. Vlookup column is "H". Please advise. eagerly waiting for your response. Thank you.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,796

    Re: V Lookup Loop taking ages to run

    Because you changed the column reference, it returns #N/A error with your attached workbook.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    06-23-2020
    Location
    India
    MS-Off Ver
    2010
    Posts
    25

    Re: V Lookup Loop taking ages to run

    Thank you so much. It worked. Thank you, thank you

    Please assist one more time in making me this code understand so that I can use it for my other columns also. I understand that in column N it will read
    values for each cell (offset(1)). Post this, if you could please make me understand the code, I will be really thankful

    With .Columns("N").Offset(1).Resize(.Rows.Count - 1)
    .Formula = "=Vlookup(H2, '" & sht1.Name & "'!$A$1:$B$" & LR & ",2,False)"
    .Value = .Value

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,796

    Re: V Lookup Loop taking ages to run

    Please Login or Register  to view this content.
    HTH

+ 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] Do While Loop taking too long
    By theTaoJones in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-08-2019, 11:18 AM
  2. [SOLVED] For Each loop taking long to run
    By Jay S. in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-07-2018, 01:08 PM
  3. Excel 2007 : Converting text to number is taking ages
    By Ruedebeuk in forum Excel General
    Replies: 18
    Last Post: 07-17-2018, 12:14 PM
  4. Cell calcs taking AGES on pactice World Cup spreadsheet
    By ExcelShadow in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-06-2014, 01:44 PM
  5. Help! lookup and datedif formula with ages and times
    By nt91 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2012, 11:32 AM
  6. Loop taking much longer - looking for alternate options
    By b2b2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-15-2012, 12:29 PM
  7. [SOLVED] Taking age group Ie ages 20-29 and picking out net sales for group
    By viabello in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-24-2006, 11:25 PM

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