+ Reply to Thread
Results 1 to 4 of 4

Dynamic VBA Sumif on Large Data - Very slow

  1. #1
    Registered User
    Join Date
    02-02-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    7

    Dynamic VBA Sumif on Large Data - Very slow

    Hi all,

    I have put a sumif in VBA and currently it is too slow for what i need it to do.

    Data sheet
    Date Category AM PM
    01/01/2019 Red 20 50
    02/01/2019 Blue 10 17
    02/01/2019 Red 5 7

    SumIF summary table:

    Date AM PM
    01/01/2019 20 50
    02/01/2019 15 24
    03/01/2019 0 0

    The above tables are simplified however the data is similar - the data is one sheet and the summary sheet is in a similar format - the columns will be identifical so might help with looping the data. The table structure is almost identical in the data sheet and the summary sheet in terms of columns
    Here is the code that i have so far but is very slow. I loop every column and row in the summary sheet and put a sumif in their, as the column changes in the summary sheet so does the data sheet as the column structure is the same.

    Basically in the summary sheet i want sumif based on the date and as the loop changes column in the summary sheet, so does the sum range in the data sheet as the columns are the same so i dont need to check the criteria in the columns AM/PM

    Please Login or Register  to view this content.
    Any help appreciated

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Dynamic VBA Sumif on Large Data - Very slow

    You can use Pivot tables, or use sumif function in VBA instead of looping. Could you attach a sample which mimcks your real data?

  3. #3
    Registered User
    Join Date
    02-02-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Dynamic VBA Sumif on Large Data - Very slow

    Hi,

    Example file attached of data layout

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Dynamic VBA Sumif on Large Data - Very slow

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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