+ Reply to Thread
Results 1 to 13 of 13

Count Week numbers given start and finish week number

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Count Week numbers given start and finish week number

    Dear forum,
    Attached a file with week numbers in column BM and BM. My aim was to count the week numbers from week start(BM) to week end (BN). I have inserted following VBA and it works.
    Please Login or Register  to view this content.
    The problem is that in my file I have more than 1000 lines and when applying the code , as mention in the code it clears all the 1000 lines and re count again though i have lines without any changes (Start, end week numbers) and it takes lot of time. Please help me to modify the code so that the VBA apply only to lines where there have been changes in week start and week end numbers so that the VBA catter only for changes or addition of new lines.
    Thanking you for your help.

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,322

    Re: Count Week numbers given start and finish week number

    The changes in weeknumbers, are you do this manuel or delivered by formula ?

    Kind regards
    Leo

  3. #3
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Count Week numbers given start and finish week number

    Your code takes 5.8 sec to execute. Following takes 0.1 sec so recalculating won't be much of an issue anymore.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  4. #4
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Re: Count Week numbers given start and finish week number

    Hi Leo,
    It is by formula.

  5. #5
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Re: Count Week numbers given start and finish week number

    Hi bakerman2,
    Thanks for the proposal, I have tested it, display result very quick. The problem is that it remove the formula in BM and BM ( Calculate week number with respect to date in BK and BL. These date also are the result a logical formula inserted meaning that these dates can change if one of the parameters changes. Can we do same action without removing the formula in BM & BN. Many thanks for your help.

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Count Week numbers given start and finish week number

    Try this.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Re: Count Week numbers given start and finish week number

    Hi bakerman2,
    It works perfectly and is very quick. At the end of each click on the button it gives a message box named Microsoft Excel with a figure in it (not same every time , i,e 0.00645 etc) What does it mean? can we remove it.
    Thank
    Vassen

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Count Week numbers given start and finish week number

    Sorry, my bad. Those are the remains of timer function to see how long code takes to execute.
    Remove lines

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Re: Count Week numbers given start and finish week number

    Hi bakerman2
    Super, perfect match of what I required, many thanks, I have not been able to add reputation(It does not allow me to do so) Thanks again
    Cheers
    Vassen

  10. #10
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Count Week numbers given start and finish week number

    You already did a few days ago, probably that's why it's not working now. Anyway thanks for adding those rep points and you're welcome.

  11. #11
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Re: Count Week numbers given start and finish week number

    Hi Bakerman2,

    When modifying the cell ref (changing from BO3 to Q4, replacing BM and BN by O4 and P) in VBA to apply same in other workbook, it gives me this message
    Runtime error 9 subscript out of range and when i click debug it give me below (with this line just before NEXT in yellow --resultarray(i, j) = j
    Can you please help

    Please Login or Register  to view this content.
    Last edited by Vassen; 09-26-2016 at 07:26 AM. Reason: resend

  12. #12
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Count Week numbers given start and finish week number

    (changing from BO3 to Q4, replacing BM and BN by O4 and P)
    That's a bit contradictory to the code you posted. Could be that's where the error comes from.
    Can you post example file with some data to test on ?
    Last edited by bakerman2; 09-27-2016 at 07:25 AM.

  13. #13
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Re: Count Week numbers given start and finish week number

    Hi Bakerman,
    In the file i was working there were some empty cells in betweem filled ones in column O and P, and when delted, VBA was ok , it works perfectly. Thanks you for your help
    Regards
    Vassen

+ 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] Week number from a custom start date
    By orleansoscar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-19-2016, 09:46 AM
  2. [SOLVED] Macro that displays all the weeks between Start Week and End Week (ends in next year)
    By Tona in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-31-2014, 05:38 AM
  3. Sheets for every week + start and ending date of the week
    By wimexcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2012, 01:48 PM
  4. [SOLVED] Week Start and Week End date
    By mangesh in forum Excel General
    Replies: 2
    Last Post: 11-22-2012, 03:15 AM
  5. Show Start of Week End of Week
    By djblois1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-13-2012, 12:31 PM
  6. week start date and end date based on week number
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2009, 12:39 PM
  7. [SOLVED] Add start of new week date when new week starts
    By Maddoktor in forum Excel General
    Replies: 1
    Last Post: 06-03-2005, 11:05 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