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

1. ## [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. ## 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?

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

Originally Posted by Richard Buttrey
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. ## Re: [Help Requested] Writing Simple Macro for 9 Week Due Date

try this:

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

5. ## 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. ## 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.``

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

Originally Posted by bulina2k
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. ## 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!

9. ## 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.

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