| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add days to date
I want the code to pick dates from column Q (see line 4) add 7 days and put
the new dates into column k. Any suggestions? Sub copy_Dates() Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536") Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value Sheets(1).Select Range("k65536").End(xlUp).Activate If ActiveCell.Value <= Now - 30 Then msgbox "NICE!!" Else: Exit Sub End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add days to date
Hi,
Use the DateAdd method. This adds 7 days to a date in Col Q and puts it in Col K Sub Copy_Dates() Set sht = Sheets(1) Dim LastRow As Long Dim c As Range LastRow = sht.Cells(Cells.Rows.Count, "Q").End(xlUp).Row Set MyRange = sht.Range("Q4:Q" & LastRow) For Each c In MyRange If IsDate(c) Then c.Offset(, -6).Value = DateAdd("d", 7, c.Value) End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tomjoe" wrote: I want the code to pick dates from column Q (see line 4) add 7 days and put the new dates into column k. Any suggestions? Sub copy_Dates() Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536") Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value Sheets(1).Select Range("k65536").End(xlUp).Activate If ActiveCell.Value <= Now - 30 Then msgbox "NICE!!" Else: Exit Sub End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add days to date
Hi Mike
Worked like a charm. Awsome simple and terrible smart. Thank you so much. Mike H skrev: Hi, Use the DateAdd method. This adds 7 days to a date in Col Q and puts it in Col K Sub Copy_Dates() Set sht = Sheets(1) Dim LastRow As Long Dim c As Range LastRow = sht.Cells(Cells.Rows.Count, "Q").End(xlUp).Row Set MyRange = sht.Range("Q4:Q" & LastRow) For Each c In MyRange If IsDate(c) Then c.Offset(, -6).Value = DateAdd("d", 7, c.Value) End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tomjoe" wrote: I want the code to pick dates from column Q (see line 4) add 7 days and put the new dates into column k. Any suggestions? Sub copy_Dates() Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536") Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value Sheets(1).Select Range("k65536").End(xlUp).Activate If ActiveCell.Value <= Now - 30 Then msgbox "NICE!!" Else: Exit Sub End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add days to date
Glad i could help and thanks for the feedback
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tomjoe" wrote: Hi Mike Worked like a charm. Awsome simple and terrible smart. Thank you so much. Mike H skrev: Hi, Use the DateAdd method. This adds 7 days to a date in Col Q and puts it in Col K Sub Copy_Dates() Set sht = Sheets(1) Dim LastRow As Long Dim c As Range LastRow = sht.Cells(Cells.Rows.Count, "Q").End(xlUp).Row Set MyRange = sht.Range("Q4:Q" & LastRow) For Each c In MyRange If IsDate(c) Then c.Offset(, -6).Value = DateAdd("d", 7, c.Value) End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tomjoe" wrote: I want the code to pick dates from column Q (see line 4) add 7 days and put the new dates into column k. Any suggestions? Sub copy_Dates() Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536") Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value Sheets(1).Select Range("k65536").End(xlUp).Activate If ActiveCell.Value <= Now - 30 Then msgbox "NICE!!" Else: Exit Sub End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add days to date
Or just add 7 (since you're adding days):
If IsDate(c) Then with c.Offset(, -6) .Value = .value + 7 end with End If Mike H wrote: Hi, Use the DateAdd method. This adds 7 days to a date in Col Q and puts it in Col K Sub Copy_Dates() Set sht = Sheets(1) Dim LastRow As Long Dim c As Range LastRow = sht.Cells(Cells.Rows.Count, "Q").End(xlUp).Row Set MyRange = sht.Range("Q4:Q" & LastRow) For Each c In MyRange If IsDate(c) Then c.Offset(, -6).Value = DateAdd("d", 7, c.Value) End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tomjoe" wrote: I want the code to pick dates from column Q (see line 4) add 7 days and put the new dates into column k. Any suggestions? Sub copy_Dates() Sheets(2).Range("e4:e65536").Value = Sheets(1).Range("d4:d65536") Sheets(1).Range("n4:Q65536").Value = Sheets(2).Range("f4:i65536").Value Sheets(1).Range("k4:K65536").Value = Range("q4:q65536").Value Sheets(1).Select Range("k65536").End(xlUp).Activate If ActiveCell.Value <= Now - 30 Then msgbox "NICE!!" Else: Exit Sub End If End Sub -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to pull <=14 Days, <=30 Days, 30 Days from a date column | Excel Discussion (Misc queries) | |||
| Conditional Formatting Dates calculating 10 days and 30 days from a certain date | Excel Worksheet Functions | |||
| Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
| TWO DATES,150 DAYS APART,NEED ONE DATE TO TURN RED AFTER 150 DAYS | Excel Programming | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
Linear Mode
