[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?

try this:

Please Login or Register  to view this content.

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.

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.

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!

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