+ Reply to Thread
Results 1 to 3 of 3

Macro to execute Sumifs or other formula faster

  1. #1
    Registered User
    Join Date
    01-11-2014
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2010
    Posts
    46

    Macro to execute Sumifs or other formula faster

    Hi Everyone,

    I attached an excel file which I hope it can demonstrate the problem.

    The main objective is to match items based on various criteria located in different columns. I have created various "SUMIFS" depending on these criteria. All matched items are marked as "YES" and then user will opt to move out the items to different sheet.

    My problem is, at the time if copying the sumif formula, it takes very long time and sometimes excel will no longer respond. Is there a way we can code this with macros so that it will run faster?

    I use the below code to update/copy the SUMIF function to all rows for one of the criteria. This code is repeated with different criteria and cell where the formula is copied from.

    Please Login or Register  to view this content.

    I hope someone can find solution.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-18-2014
    Location
    australia
    MS-Off Ver
    2015
    Posts
    43

    Re: Macro to execute Sumifs or other formula faster

    the short answers are

    yes, it freezes currently
    yes, VBA will be better
    yes, you can use your formulas in VBA with some modifications

    i would write each formula into a function, then call the correct function based on the user input.
    Last edited by ferday; 02-14-2017 at 02:13 AM.

  3. #3
    Registered User
    Join Date
    01-11-2014
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Macro to execute Sumifs or other formula faster

    Hi ferday,

    I'm a newbie in VBA. Can you write me how to write each formula into a function?

+ 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. Macro to execute Sumifs furmula faster
    By ALNER in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2017, 03:47 AM
  2. Execute a macro in a closed workbook, or open and execute without the user noticing
    By cearaujo123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2015, 06:44 PM
  3. Faster alternative to sumifs and averageifs needed
    By BlueDan2015 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2015, 06:44 AM
  4. Macro efficiency - Execute process faster
    By Zoediak in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-08-2014, 02:37 PM
  5. Replies: 7
    Last Post: 07-01-2014, 08:15 AM
  6. Formula? Or Macro? Which is faster?
    By ad9051 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-15-2011, 09:50 AM
  7. faster way to get to result - formula/macro
    By tx12345 in forum Excel General
    Replies: 1
    Last Post: 09-16-2006, 02:55 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07503 seconds
  • Memory Usage 8,924KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (3)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (3)postbit_legacy
  • (3)postbit_onlinestatus
  • (3)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1