Results 1 to 10 of 10

Replacement VBA for sumif on a large set of data - taking too long to calcuate

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-07-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    126

    Replacement VBA for sumif on a large set of data - taking too long to calcuate

    Hi all,

    I have a VBA code that runs a lot of formulas and variables over a large set of data (over 50k rows). When I have gone to run the formula it is taking around 10minutes to complete each time, through process of elimination (commenting out segments of coding bits at a time) I have deduced that it is the following code that is slowing it down which uses sumif formulas.

    Range("T2").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(C18,RC19,C11)"
        Range("T2:T" & Range("AD3").Value).FillDown
    
        Range("X2").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(C22,RC23,C11)"
        Range("X2:X" & Range("AD4").Value).FillDown
    
        Range("AB2").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(C26,RC27,C11)"
       Range("AB2:AB" & Range("AD5").Value).FillDown

    I have searched through the forum and have found some helpful threads which replace the sumif formulas with different coding (scripting dictionaries etc) but, my VBA knowledge is intermediate at best and I am struggling to understand the coding, I wondered if anyone could help me implement this into my code to speed the VBA and whilst explaining the coding at the same time so I know what it is doing for future reference?

    TIA
    Last edited by elleb; 04-21-2020 at 12:03 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 32
    Last Post: 03-29-2024, 07:01 PM
  2. [SOLVED] Excel Formula in Large File Taking Too Long to Process
    By ziggyztz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-29-2019, 01:02 PM
  3. Replies: 1
    Last Post: 01-24-2015, 08:29 PM
  4. [SOLVED] =IF and =SUMIF formulas creating long long long data processing times.
    By comp in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 03-26-2014, 02:59 PM
  5. Large Data Replacement
    By tconnell1965 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2013, 02:39 PM
  6. Optimizing Formulas - taking too long to calculate large worksheet
    By lightsandsirens in forum Excel General
    Replies: 0
    Last Post: 08-21-2012, 10:38 AM
  7. Better way to transfer data my way taking a long time
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-06-2011, 06:03 AM

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