Auto date fill

[dainesy]

Hello,

Using excel.

I'm looking to compose a macro to automatically fill a date range for example
if c1 = 10/10/09 then d1 = one week after "17/10/09" e1 = 2 weeks after "24/10/09" and so on until 4 weeks time.
also if b1 = w then above script kicks in but if b1 = m then script will not run.

One other thing, this is for the whole spreadsheet, the exact ref range will be

if d10= w then autofil with date range e10 f10 g10 h10
if d11= w then autofil with date range e11 f11 g11 h11
and so on...

Any help would be much apprieciated

[Palmetto]

See if the attached is what you intended. There is a conflict in the columns you referenced in your example so I have assumed Column-D holds "m" or "w" and Column-E holds the intial date entry.

In the worksheet_Change event;

Please Login or Register  to view this content.

[JBeaucaire]

You can accomplish the same thing with no macro, just worksheet formulas, if you're interested.

In E10:
=IF(AND($B10="w", $D10="w", $C10>0), $C10+7, "")

In F10:
=IF($E10="", "", E10+7
Copy that cell across into G10 and H10.

Then copy those four cells down as far as you want.

[dainesy]

This hasn't quite worked, if i include an attachment of my work sheet it my help you to see what i need.

thank you.

[Palmetto]

Code amended. Got the columns wrong as I was a bit confused by your initial post. Should work now, but you'll need to copy the code to each sheet module.

Please Login or Register  to view this content.

I agree with JBeaucaire on the use of formulas, as they are always faster and more efficient than VBA code.