+ Reply to Thread
Results 1 to 5 of 5

Speed up an Array formula in a macro

  1. #1
    Registered User
    Join Date
    01-28-2015
    Location
    New Haven, CT
    MS-Off Ver
    Office 2010
    Posts
    8

    Speed up an Array formula in a macro

    Hello,

    I have a macro that has an array code in one cell (Cell C5), and I want to drag that formula a couple of columns and many rows down (around 20,000 rows). The formula takes so long to calculate (and ultimately doesn't ever calculate because I have to quit it). The array formula is essentially looking up a person's name, and finding the different organizations the person is associated with (from a different tab). The array uses the person's name (Column 1), and the number for the array (1,2,3, etc. in row 4). Is there any way to speed it up?

    Right now the code is as follows:
    "LC" corresponds to the letter of the last column of my data (right now it is "E")
    "LR" corresponds to the last row of my data (right now it is 22,091)

    Please Login or Register  to view this content.
    Additionally, I have the following codes at the beginning to speed up the code:
    Please Login or Register  to view this content.

    Is there any way to make this faster (so that it is actually capable of being run)?

    Thank you so much!
    Molly
    Last edited by molly13; 02-25-2016 at 12:40 PM.

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Speed up an Array formula in a macro

    That's a beastly looking formula. It might be more feasible to write a procedure that does what your formula is attempting to do, rather than trying to implant tens of thousands of formula cells. If you're open to that route, it would help to post a small sample workbook so we can see what you have and get a better sense of what you're after.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Speed up an Array formula in a macro

    I agree with cantosh, that is a beastly looking formula. At first glance, it looks like its purpose is to extract some subset of a datarange from the larger datarange.

    Before writing your own procedure, I would check to see if there are built in utilities that will do what you want (or will do the difficult/processor intensive data management parts). Pivot tables and filters are often much better and faster at extracting and summarizing subsets of a database. Even if the filter/pivot cannot get you the final result you need, it may be much more efficient at pulling the data out of the database that you need to get your final result.

    If you decide that a single cell mega formula really is the way to go, then do a couple of things to try to improve it.

    1) Where possible, use helper column/cells to minimize duplicated effort.
    2) Don't use full column references (C1 for example). Limit references to a reasonable size.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    01-28-2015
    Location
    New Haven, CT
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Speed up an Array formula in a macro

    Hi cantosh and mrshorty,

    Thanks so much for the insight. I scrapped the formula and instead am using helper columns with a Vlookup and the calculation is now nearly instantaneous.

    Thank you!
    Molly

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Speed up an Array formula in a macro

    Impressive, to go from "essentially unusable" to "nearly instantaneous." Score +1 for using helper columns.

+ 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. Can anyone tell me how to speed up this array?
    By Ppessina in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2014, 06:02 PM
  2. Array looping, increase speed of array macro
    By techrcn in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-27-2013, 05:33 AM
  3. speed up sum if array formula with VBA??
    By jed38 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2013, 01:51 PM
  4. How to speed up this macro? How to use an array?
    By djvino in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-15-2012, 07:39 AM
  5. Macro Needed To Speed Up A Formula
    By JimmiOO in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-19-2012, 12:13 PM
  6. Convert Array formula to VBA to speed up worksheet
    By MarathonMan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-11-2007, 06:17 PM
  7. Will an Array speed this up?
    By Mase in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-04-2005, 06:53 AM

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