+ Reply to Thread
Results 1 to 7 of 7

WorksheetFunction - cycle through different values

  1. #1
    Registered User
    Join Date
    01-30-2020
    Location
    UK
    MS-Off Ver
    2013
    Posts
    82

    WorksheetFunction - cycle through different values

    Hi All,

    I'm in need of some help to see if I can run through my code a bit niftier.

    I need to cycle through the following code with each 'Bucket'. The Code is exactly the same apart from the 'Bucket' value changing. Could there a better way to state the code once and run through it in memory for each Bucket value? I originally listed all 17 versions of the code together and encountered 'procedure too large' error.

    I am aware that I need to reset the LRpw long value each time to to ensure the results don't just constantly overwrite each other and do appear below each other.

    Any help appreciated as always!

    Please Login or Register  to view this content.
    Last edited by warp765; 04-01-2020 at 07:49 AM. Reason: SOLVED

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,688

    Re: WorksheetFunction - cycle through different values

    You want people to go through 140+ lines of code.
    Maybe you should explain in a consise manner what you want to do.
    And maybe attach a workbook with a before and after explaining how you arrived at the "after".

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: WorksheetFunction - cycle through different values

    His code clearly shows Bucket1 through Bucket17. I believe he is looking for a way to use the one set of posted "Bucket1" code to cycle through all 17 bucket variables. Maybe?

  4. #4
    Registered User
    Join Date
    01-30-2020
    Location
    UK
    MS-Off Ver
    2013
    Posts
    82
    Quote Originally Posted by JBeaucaire View Post
    His code clearly shows Bucket1 through Bucket17. I believe he is looking for a way to use the one set of posted "Bucket1" code to cycle through all 17 bucket variables. Maybe?
    Hi both, thanks for your replies.

    Yes, the above is correct! I would like to run the code I have for each bucket variable i.e after the code is run first time I want to run it again but change for Bucket2 then 3 then 4 etc, until all 17 are done.

    I wondered if that could be done without stating 17x the code and instead the change of that string value could be made in memory?

    Cheers

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: WorksheetFunction - cycle through different values

    So this is the idea, untested. I've highlighted my edits in red, I removed the Bucket variables and all the Offset(1, 0) references, easier to simply fix the LRbw from the start.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-30-2020
    Location
    UK
    MS-Off Ver
    2013
    Posts
    82

    Re: WorksheetFunction - cycle through different values

    @JBeaucaire, a lovely simple but effective solution using the array, thank you so much!. Rep added and thread closed.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: WorksheetFunction - cycle through different values

    Glad it works for you. Cheers.

+ 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] Current code to cycle through sheets and copy values is very slow - can it be sped up?
    By maym in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-29-2017, 11:28 PM
  2. Replies: 2
    Last Post: 08-25-2017, 03:46 AM
  3. Cycle through dropdown list and paste values
    By hoyasaxa215 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2014, 10:33 AM
  4. Macro to Cycle data and extract those values that meet criteria
    By Thomo88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-19-2013, 12:29 PM
  5. Replies: 0
    Last Post: 12-18-2012, 05:40 AM
  6. cycle through list of data & use values to trigger an action
    By stn5006 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2011, 07:36 PM
  7. [SOLVED] How do I keep result from 1 iteration cycle to use in next cycle?
    By sgl8akm in forum Excel General
    Replies: 0
    Last Post: 07-27-2006, 03:35 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.07423 seconds
  • Memory Usage 9,100KB
  • Queries Executed 15 (?)
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
  • (2)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (7)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (7)postbit_legacy
  • (7)postbit_onlinestatus
  • (7)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
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (48):
  • 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_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