Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dating cells in a worksheet using VBA
I wish to use VBA to automatically date selected cells.The dates are in
ascending order and are separated by a fixed number of days. For example :- Date cells A1 , A10 , A20 , A30 with 1 Jan 2006 , 8 Jan 2006 , 15 Jan 2006 , 22 Jan 2006. My experience in VBA is almost non-existent in this area and I would appreciate some help on this matter. Any Suggestions please ? -- FLYNNE |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dating cells in a worksheet using VBA
nDate = DateValue(2006,1,1)
With ActiveSheet .Range("A1").Value = nDate .Range("A10").Value = nDate + 7 .Range("A20").Value = nDate + 14 .Range("A30").Value = nDate + 21 End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FLYNNE" wrote in message ... I wish to use VBA to automatically date selected cells.The dates are in ascending order and are separated by a fixed number of days. For example :- Date cells A1 , A10 , A20 , A30 with 1 Jan 2006 , 8 Jan 2006 , 15 Jan 2006 , 22 Jan 2006. My experience in VBA is almost non-existent in this area and I would appreciate some help on this matter. Any Suggestions please ? -- FLYNNE |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dating cells in a worksheet using VBA
Thanks Bob for your rapid replys.
I wish to clarify what I am tring to do.. In essence I wish to run VBA code to accomplish the following :- 1. To copy a details box ( a table containg Name ,Date and other details) for each day of the year i.e obtain 365 identical boxes except for the date. This I have already accomplished by running the following code. Sub Macro2() ' ' Macro2 Macro ' Macro recorded 10/07/2006 by j.winney ' ' Application.ScreenUpdating = False Application.DisplayAlerts = False Range("A1").Select For t = 1 To 13140 Step 36 Selection.CurrentRegion.Select Selection.Copy Range("A37")(t).Select ActiveSheet.Paste Application.CutCopyMode = False Next t Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub 2. I want to insert the date for each box concurrently automatically using VBA viz just like a diary or calendar. This is the bit I am unable to do at present. I hope these details make things a bit clearer. Regards -- FLYNNE "Bob Phillips" wrote: nDate = DateValue(2006,1,1) With ActiveSheet .Range("A1").Value = nDate .Range("A10").Value = nDate + 7 .Range("A20").Value = nDate + 14 .Range("A30").Value = nDate + 21 End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FLYNNE" wrote in message ... I wish to use VBA to automatically date selected cells.The dates are in ascending order and are separated by a fixed number of days. For example :- Date cells A1 , A10 , A20 , A30 with 1 Jan 2006 , 8 Jan 2006 , 15 Jan 2006 , 22 Jan 2006. My experience in VBA is almost non-existent in this area and I would appreciate some help on this matter. Any Suggestions please ? -- FLYNNE |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dating cells in a worksheet using VBA
Hello Bob
I tried your chunk of code - it worked perfectly - Thanks. Is it possible to modify this code (using a loop maybe) so that consecutive dates can be added to each table in turn. I would like to send you a copy of the file I am working on. How does one do this in this newsgroup ? Regards -- FLYNNE "FLYNNE" wrote: Thanks Bob for your rapid replys. I wish to clarify what I am tring to do.. In essence I wish to run VBA code to accomplish the following :- 1. To copy a details box ( a table containg Name ,Date and other details) for each day of the year i.e obtain 365 identical boxes except for the date. This I have already accomplished by running the following code. Sub Macro2() ' ' Macro2 Macro ' Macro recorded 10/07/2006 by j.winney ' ' Application.ScreenUpdating = False Application.DisplayAlerts = False Range("A1").Select For t = 1 To 13140 Step 36 Selection.CurrentRegion.Select Selection.Copy Range("A37")(t).Select ActiveSheet.Paste Application.CutCopyMode = False Next t Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub 2. I want to insert the date for each box concurrently automatically using VBA viz just like a diary or calendar. This is the bit I am unable to do at present. I hope these details make things a bit clearer. Regards -- FLYNNE "Bob Phillips" wrote: nDate = DateValue(2006,1,1) With ActiveSheet .Range("A1").Value = nDate .Range("A10").Value = nDate + 7 .Range("A20").Value = nDate + 14 .Range("A30").Value = nDate + 21 End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FLYNNE" wrote in message ... I wish to use VBA to automatically date selected cells.The dates are in ascending order and are separated by a fixed number of days. For example :- Date cells A1 , A10 , A20 , A30 with 1 Jan 2006 , 8 Jan 2006 , 15 Jan 2006 , 22 Jan 2006. My experience in VBA is almost non-existent in this area and I would appreciate some help on this matter. Any Suggestions please ? -- FLYNNE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dating a file. | Excel Discussion (Misc queries) | |||
How do I change from american dating to uk dating? | Excel Worksheet Functions | |||
How to update dating? | New Users to Excel | |||
Dating Problem | Excel Programming | |||
dating problems! | Excel Worksheet Functions |