+ Reply to Thread
Results 1 to 9 of 9

[Help Requested] Writing Simple Macro for 9 Week Due Date

  1. #1
    Registered User
    Join Date
    09-30-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    5

    Question [Help Requested] Writing Simple Macro for 9 Week Due Date

    Hello all,

    I am new to macros and I need some help writing a macro to calculate due dates based on an existing date.

    Here is what I am working with...

    9WeekMacro.jpg

    I started by recording this macro, but I found out from a friend that when you record a macro all of the cell references are absolute references. I want to make it so no matter how many rows of existing dates there are, or where the dates are on a worksheet, the cells next to each date will populate with the nine week date.

    My thought was I could record the macro then go back and edit out the absolute references in favor of something more universal, I just don't know what that is or how to do it.

    Thank you for helping me learn and troubleshoot!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,960

    Re: [Help Requested] Writing Simple Macro for 9 Week Due Date

    Hi,

    Unless you want this formula in various columns, do you really need a macro at all? Why not just enter =E2+63 in F2 and copy it down?
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-30-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    5

    Re: [Help Requested] Writing Simple Macro for 9 Week Due Date

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Unless you want this formula in various columns, do you really need a macro at all? Why not just enter =E2+63 in F2 and copy it down?
    That is a valid point. However, I am trying to learn macros and I am starting simple. Plus, I am making this macro for a person who may not be savvy enough to do the =E2+63 autofill option.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,520

    Re: [Help Requested] Writing Simple Macro for 9 Week Due Date

    try this:

    Please Login or Register  to view this content.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,960

    Re: [Help Requested] Writing Simple Macro for 9 Week Due Date

    OK,

    Then in a macro why not simply

    Please Login or Register  to view this content.
    Stuff like .Select, Selection, .Activate etc. is rarely needed and just clogs up code. You'll find the macro recorder gives you all sorts of stuff you don't need and should always be edited out as necessary.

  6. #6
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2016 and 365
    Posts
    843

    Re: [Help Requested] Writing Simple Macro for 9 Week Due Date

    Or if the date is not on "E" column... actually if the date is anywhere in the worksheet you might try
    Please Login or Register  to view this content.
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  7. #7
    Registered User
    Join Date
    09-30-2015
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    5

    Re: [Help Requested] Writing Simple Macro for 9 Week Due Date

    Quote Originally Posted by bulina2k View Post
    Or if the date is not on "E" column... actually if the date is anywhere in the worksheet you might try
    Please Login or Register  to view this content.
    This does exactly what I wanted it to! Thank you!

    Since I am trying to learn, would you mind giving a quick explanation of each part of the macro? To me, it is complicated, but if I start to understand the reasoning behind it I will be able to write them myself. Thank you, again!

  8. #8
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2016 and 365
    Posts
    843

    Re: [Help Requested] Writing Simple Macro for 9 Week Due Date

    okaaay.. let me try to explain line by line
    For Each cel In ActiveSheet.Cells.SpecialCells(xlConstants)
    this line starts a loop referencing every cell in the activesheet. Well not quite every cell but every cell that has a constant in it (SpecialCells(xlConstants) - so it skips cells with formula and also skips empty cells)
    But then not every cell with constant inside is a date is it? Let's verify! -> If IsDate(cel) Then
    So if the current cell is a date we put in the cell right next to it (cel.Offset(, 1)) the formula RC[-1] + 63
    RC[-1] stands for "same row, 1 column to the left" (that will be our date!)
    as R[1]C[1] stands for "1 row down, 1 column right"


    Oh and thank you for the rep. Don't forget to have fun!
    Last edited by bulina2k; 01-25-2016 at 11:54 AM. Reason: thanks for the rep point

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,960

    Re: [Help Requested] Writing Simple Macro for 9 Week Due Date

    ...but a word of caution. Macro code with loops that reference sheet cells is always slower than code without loops since there is a time overhead every time the macro jumps back to the Excel Application and then returns to the VB Environment.

    Unless there's some over-riding need to use a loop then avoid them. With large ranges, which I accept may not be the case here, they will catch you out and you'll wonder why things are taking a long time.

+ 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. Replies: 13
    Last Post: 10-10-2014, 06:50 AM
  2. Simple chart help requested from newbie!
    By Russ and his BM in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-03-2009, 07:12 AM
  3. 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
  4. Date formula help requested pls.
    By markmash in forum Excel General
    Replies: 4
    Last Post: 04-04-2009, 08:01 PM
  5. [SOLVED] Writing a simple macro or formula
    By Robin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-28-2006, 07:15 PM
  6. Assistance Requested for Date Arithmetic
    By David Lipetz in forum Excel General
    Replies: 0
    Last Post: 01-24-2006, 01:45 PM
  7. need help in writing simple macro
    By rkg in forum Excel General
    Replies: 4
    Last Post: 05-04-2005, 07:03 PM

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