+ Reply to Thread
Results 1 to 17 of 17

VBA to use different function other than looping to make code run faster

  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,168

    VBA to use different function other than looping to make code run faster

    I recently got introduced to coding with filtering which if designed correctly can do most of the work certain looping codes do and would result in faster processing.
    So here is a code i use with looping:
    Please Login or Register  to view this content.
    This code takes forever since there is a lot of information the code needs to loop through.
    Since i only got introduced to filtering i don't have any idea on how to convert the above code to work via filter.

    These are the steps in the code above written out:
    1) The code enters sheets "Pending Orders"
    *might be helpful to note that the information on "Pending Orders" starts from row 5 and row 4 is the header, also the part of the code that i need copied is every odd row but vital information pertaining to every odd row exists on the row beneath it which is an even row.
    2) The code finds the word "ORDER CANCELLED" which will exist on only an even row
    3) If the code finds "ORDER CANCELLED" it will then move to the odd row right above it and copy this:
    Please Login or Register  to view this content.
    4) Next the code enters sheets "Report Center" and pastes the copied information into row 5 and so on, meaning it copies the first loop match into row 5 and the 2nd loop match into row 6 and the 3rd into row 7 and so on and so forth.

    Any help on making this work with filtering would be greatly appreciated.
    Last edited by kosherboy; 12-10-2015 at 12:48 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,168

    Re: VBA to use different function other than looping to make code run faster

    "Hold off! There is a slight mistake in my code, i will edit now...."

    (a couple minutes later....)

    "Ok, fixed now"
    Last edited by kosherboy; 12-10-2015 at 12:36 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: VBA to use different function other than looping to make code run faster

    Hi

    Tip 1:
    I think that the UNION( ) statement could omit the "ws2" references since you already have a "With ws2" stamens earlier.
    This may be a bit faster:
    Please Login or Register  to view this content.
    Tip 2:
    Switching to use CELLS() rather than RANGE() might be more efficient as it avoids those string concatenations;
    it may be worth investigating.
    Please Login or Register  to view this content.
    If this has been helpful, please click on the star at the left.

  4. #4
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: VBA to use different function other than looping to make code run faster

    And here's Tip 3:

    Change the FOR loop to use the STEP 2 option so it counts by 2 automatically.
    Then you do not need the IF rcell MOD 2 = 1 calculation to determine odd row #s
    Please Login or Register  to view this content.

  5. #5
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,206

    Re: VBA to use different function other than looping to make code run faster

    Rather than
    Please Login or Register  to view this content.
    You could use

    Please Login or Register  to view this content.
    Rather than
    Please Login or Register  to view this content.
    you could use
    Please Login or Register  to view this content.
    [/CODE]
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,168

    Re: VBA to use different function other than looping to make code run faster

    Quote Originally Posted by StuCram View Post
    And here's Tip 3:

    Change the FOR loop to use the STEP 2 option so it counts by 2 automatically.
    Then you do not need the IF rcell MOD 2 = 1 calculation to determine odd row #s
    Please Login or Register  to view this content.
    Thank you for those tips, however the code still took the same amount of time to run.

  7. #7
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: VBA to use different function other than looping to make code run faster

    And here's tip # 5

    Set a range to the initial group of cells to be copied
    Then in the loop, use the OFFSET() method to adjust it quickly to the desired group on the new row.
    New / modified lines of code are shown in bold below.
    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,168

    Re: VBA to use different function other than looping to make code run faster

    Quote Originally Posted by StuCram View Post
    And here's tip # 5

    Set a range to the initial group of.....
    Thank you again.
    The debugger went on and the ".range" got highlighted:
    Please Login or Register  to view this content.
    With this message:
    "Compile error:
    Wrong number of arguments or invalid property assignment"

  9. #9
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: VBA to use different function other than looping to make code run faster

    OOPS - my carelessness. Far too many quotation marks.
    I also noticed a small typo by me: the A5 near the end of the statement should be AA5, I think.
    Try this change:

    Please Login or Register  to view this content.
    or this shorter form (incorporating tip from Mike Erikson)

    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,168

    Re: VBA to use different function other than looping to make code run faster

    I appreciate all your help but i still don't see any difference in the timing for the code to run

  11. #11
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,168

    Re: VBA to use different function other than looping to make code run faster

    Was wondering if it's possible to use the filter?

  12. #12
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: VBA to use different function other than looping to make code run faster

    Those previous tips have helped in some of my projects but of course every situation is different.

    I don't have much experience (yet) using filters.
    That usually speeds up many tasks but here you are working with 2 rows at a time so filtering may not work.
    Sigh.

    Hope you find a solution and share it.

  13. #13
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,168

    Re: VBA to use different function other than looping to make code run faster

    Ok, thank you once again for your time on this

  14. #14
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to use different function other than looping to make code run faster

    Hi kosherboy

    How about posting a Sample of your Data so we can play with it.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  15. #15
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,628

    Re: VBA to use different function other than looping to make code run faster

    Try this to see if this helps...
    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  16. #16
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,168

    Re: VBA to use different function other than looping to make code run faster

    Dear sktneer,
    There is definitely a noticeable change in speed with the filtering
    I appreciate your code very much

  17. #17
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,628

    Re: VBA to use different function other than looping to make code run faster

    You're welcome. Glad I could offer some help.
    Thanks for the feedback as well.

+ 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] Make code runs faster
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-02-2015, 03:42 PM
  2. [SOLVED] Make this code more efficient and faster
    By SIMBAtheCAT in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2014, 10:45 AM
  3. [SOLVED] Make code Faster
    By zplugger in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-30-2013, 10:08 AM
  4. Make VBA code faster
    By Danielle22 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-28-2013, 09:01 AM
  5. Make the below code work faster!!!
    By devpp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-04-2013, 06:13 AM
  6. [SOLVED] Make code run faster
    By ozhunter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2013, 04:26 AM
  7. Is it possible to get rid of this loop to make the code faster?
    By mhw1129 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-09-2011, 09: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