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

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

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

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

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