| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Date Cell
Hi All,
I have the following macro in excel. Sub Macro() Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End Sub This to to rotate names through a 17 week shift rosta when macro is run. What I need is I have a week commencing date in cell A10, e.g. 04 May 2009 is there a code I can add so that when the shift rotates when I run the macro the date in Cell A10 can be replaced by the new week commencing date e.g. 11 May 2009 and so on when the macro is run. Any help is greatfully appreciated. Many thanks Terilad |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update Date Cell
Try the below. Note the new line added to increment the date. Range("A10") =
Range("A10") + 7 Sub Macro() Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant Range("A10") = Range("A10") + 7 arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End Sub If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi All, I have the following macro in excel. Sub Macro() Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End Sub This to to rotate names through a 17 week shift rosta when macro is run. What I need is I have a week commencing date in cell A10, e.g. 04 May 2009 is there a code I can add so that when the shift rotates when I run the macro the date in Cell A10 can be replaced by the new week commencing date e.g. 11 May 2009 and so on when the macro is run. Any help is greatfully appreciated. Many thanks Terilad |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update Date Cell
Your a genius.
Many thanks Terilad "Jacob Skaria" wrote: Try the below. Note the new line added to increment the date. Range("A10") = Range("A10") + 7 Sub Macro() Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant Range("A10") = Range("A10") + 7 arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End Sub If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi All, I have the following macro in excel. Sub Macro() Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End Sub This to to rotate names through a 17 week shift rosta when macro is run. What I need is I have a week commencing date in cell A10, e.g. 04 May 2009 is there a code I can add so that when the shift rotates when I run the macro the date in Cell A10 can be replaced by the new week commencing date e.g. 11 May 2009 and so on when the macro is run. Any help is greatfully appreciated. Many thanks Terilad |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update Date Cell
Thanks for the feedback..
-- If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Your a genius. Many thanks Terilad "Jacob Skaria" wrote: Try the below. Note the new line added to increment the date. Range("A10") = Range("A10") + 7 Sub Macro() Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant Range("A10") = Range("A10") + 7 arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End Sub If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi All, I have the following macro in excel. Sub Macro() Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End Sub This to to rotate names through a 17 week shift rosta when macro is run. What I need is I have a week commencing date in cell A10, e.g. 04 May 2009 is there a code I can add so that when the shift rotates when I run the macro the date in Cell A10 can be replaced by the new week commencing date e.g. 11 May 2009 and so on when the macro is run. Any help is greatfully appreciated. Many thanks Terilad |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update Date Cell
Hi Jacob,
Sorry to pester you but I have noticed another range of cells which require to go forward 7 days, the code you gave me works fine for the week commencing cell, the other cells are Days of week cells displayed as Manday 04 and so on. I have moved the week comm cell data to cell N2, the other 7 cells which require to go forward 7 days a D4,F4,H4,J4,L4,N4 and P4 these cells hold days of week with date, displayed like Monday 04, Tuesday 05 and so on. Can you give me any help on this. Regards Terilad "Jacob Skaria" wrote: Try the below. Note the new line added to increment the date. Range("A10") = Range("A10") + 7 Sub Macro() Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant Range("A10") = Range("A10") + 7 arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End Sub If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Hi All, I have the following macro in excel. Sub Macro() Dim lngRow As Long Dim intTemp As Integer Dim arrData(17) As Variant arrData(0) = Range("A35") For lngRow = 3 To 35 Step 2 intTemp = intTemp + 1 arrData(intTemp) = Range("A" & lngRow) Range("A" & lngRow) = arrData(intTemp - 1) Next Range("A1") = varValue End Sub This to to rotate names through a 17 week shift rosta when macro is run. What I need is I have a week commencing date in cell A10, e.g. 04 May 2009 is there a code I can add so that when the shift rotates when I run the macro the date in Cell A10 can be replaced by the new week commencing date e.g. 11 May 2009 and so on when the macro is run. Any help is greatfully appreciated. Many thanks Terilad |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Update a date into the same cell? | Excel Worksheet Functions | |||
| Automatically update a cell with a date based on anther cells date | Excel Discussion (Misc queries) | |||
| input a date or update it based on date in another cell | New Users to Excel | |||
| Populating Last Saved Date in Cell AND also update that same cell in Header | Excel Discussion (Misc queries) | |||
| How to update a cell if a specific date is included in a date rang | Setting up and Configuration of Excel | |||
Linear Mode
