+ Reply to Thread
Results 1 to 10 of 10

Workday column loop

  1. #1
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Workday column loop

    Hi,

    I'm very new with using loops and my VBA knowledge is getting there, although rather slowly . But most of what I have learnt is from this forum, so as always thank you.

    Every year I have to create a holiday tracker and it can be quite laborious having to copy and paste formula's all the time so I thought with the knowledge that I have gained I could automate it every year by a push of a button and changing the start and end dates.

    Most of it I can do but then I hit a snag. I can't seem to loop through the columns so that when it hits the 31/01/2022 or greater the the loop ends.

    I kind of played around with the 'do while' but still can't get it to work But as I said, looping is pretty new to me.

    I attach a sample worksheet to show what I want it to do and have left some simple formula's. In theroy each column header will show a workday for the entire year upto the end date from cell A1 to cell A2.

    Any help as always will be greatly appreciated
    Attached Files Attached Files
    Last edited by AliGW; 12-06-2021 at 07:43 AM. Reason: Irrelevant section of title removed.

  2. #2
    Forum Contributor
    Join Date
    12-08-2020
    Location
    South Korea
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: Workday column loop

    This is do-while example.

    try this
    Please Login or Register  to view this content.
    Caring environment and nature. caring you.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Workday column loop

    Is what you want ? (non-VBA!)

    in C4

    =IF(WORKDAY(B4,1)>$B$2,"",WORKDAY(B4,1))

    copy across

    Previous post has loop example.
    Last edited by JohnTopley; 12-06-2021 at 08:08 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Workday column loop

    VBA

    Please Login or Register  to view this content.
    Last edited by JohnTopley; 12-06-2021 at 08:51 AM.

  5. #5
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Re: Workday column loop

    This is exactly what I want JohnTopley.

    Can you confirm if I have declared this correctly as I am Option Explicit I don't want to burn out my memory

    Please Login or Register  to view this content.

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Workday column loop

    Please Login or Register  to view this content.
    In the above declaration...
    sDate and fDate are declared as a variant
    Should be
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  7. #7
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Re: Workday column loop

    Hi sintek,

    Do you mean it should be

    Please Login or Register  to view this content.
    as you have declared as Date also?

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Workday column loop

    No...Look at Post 6 again...

    I was referencing your declaration in Post 5 ... I assumed you wanted to declare them all as dates...not so...

  9. #9
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    100

    Re: Workday column loop

    Hi Sintek,

    Sorry for the delay.

    You have to excuse my ignorance as I really don't understand the Dim statements as it is all latin to me Dim A as Long to Dim A as Variant means the same to me and I just keep changing them until one works If you know where there is a post that explains this to me in laymen's terms, then can you point me to it as it would be good for my understanding.

    If you are saying that:

    Please Login or Register  to view this content.
    is the correct way of declaring it, then that is fine. What I don't understand is what is the difference between your statement and my

    Please Login or Register  to view this content.
    I see that you have declared each individually 'as Date' whereas I have only declared mine once. Does it make any difference?

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Workday column loop

    Have a look here

    Dim sDate, fDate, wDate As Date
    This above means...
    Please Login or Register  to view this content.
    So if you do not stipulate it automatically takes on declaration of Variant

+ 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] Simple Loop until
    By PNick in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-11-2017, 11:06 AM
  2. Simple loop
    By sierpienny in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2016, 06:39 AM
  3. [SOLVED] Help with simple for loop
    By Leipzig in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-14-2013, 04:19 PM
  4. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  5. Workday.INTL or Workday function issue
    By junoon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2013, 03:14 PM
  6. [SOLVED] A simple loop
    By kbka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2012, 01:21 PM
  7. [SOLVED] Simple loop?
    By bushtor in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-13-2005, 11:05 AM

Tags for this Thread

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