+ Reply to Thread
Results 1 to 5 of 5

Macro to change a row of dates in line with current month and year

  1. #1
    Registered User
    Join Date
    01-11-2011
    Location
    London, england
    MS-Off Ver
    Excel 2003
    Posts
    23

    Macro to change a row of dates in line with current month and year

    Hello. I’ve got a row of dates and a row of day names (month) long.
    I’d like a macro that would:
    1. check the month and year of the current date and then accordinly change the date row
    2. it would check the day name (start of the month) and accordingly change the day name row.

    I’ve attached an example workbook with source and target sheet.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Macro to change a row of dates in line with current month and year

    Hi widodude,

    I think you want a Weekday in a Choose function.
    See the attached.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    01-11-2011
    Location
    London, england
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Macro to change a row of dates in line with current month and year

    That's pretty cool function thanks for that. I'm not sure I'll add another post as it's part of the problem. So i've got some code (two functions I found and modified from MSDN) and some hashed up code I wrote up. It basically cycles through a range of cells and enters incremental dates - however it doesn't stop at the End of the Month Date i think my loop needs some work.

    Please Login or Register  to view this content.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Macro to change a row of dates in line with current month and year

    Hi widodude,
    There are lots of ways to put those dates across the top of your sheet. This one uses the date in B3 and fills them in to the end of the month. I find that VBA doesn't use the same Date formula syntax as Excel so I set formulas in the Excel cells to get what I wanted and then used those numbers in the VBA.

    I'm sure one of the good gurus could make this code in about half the lines. Watersev could do it in about 3 using his codeing style.
    Please Login or Register  to view this content.
    See the attached with the code:
    BTW - I hate dates across the top as column heads. I think you will be much better off putting all the dates in a single column. This way Excel can treat your data as a List/Table and do a lot more things with it. Perhaps if you show a real example of what you are intending, we can fix your data structure so it does a lot more.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-11-2011
    Location
    London, england
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Macro to change a row of dates in line with current month and year

    Hi there thanks for that. I modified the code I had originally as such:

    Please Login or Register  to view this content.
    I do like your minimised code use. i'm obviously rubbish at that

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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