leezac
asked on
Checking for todays date
I am getting an error "Object Required on this line
If Not Intersect(Target, range("C2")) Is Nothing Then
in the below code. I am trying to show a message box if the value in C2 of Sheet21 is <> now().
Thanks in advance.
Dim ws As Worksheet
Dim r As range
Sheets("IDC").Activate
Set ws = Application.ActiveSheet
Set r = Sheet21.range("C2")
If Not Intersect(Target, range("C2")) Is Nothing Then
If r("C2").Value = "<> now()" Then MsgBox ("Future dates not allowed!")
End If
If Not Intersect(Target, range("C2")) Is Nothing Then
in the below code. I am trying to show a message box if the value in C2 of Sheet21 is <> now().
Thanks in advance.
Dim ws As Worksheet
Dim r As range
Sheets("IDC").Activate
Set ws = Application.ActiveSheet
Set r = Sheet21.range("C2")
If Not Intersect(Target, range("C2")) Is Nothing Then
If r("C2").Value = "<> now()" Then MsgBox ("Future dates not allowed!")
End If
ASKER
What if the date in the cell is formated like 20130606???
ASKER
like this?
Set r = Sheet21.range("C2")
If r.Value = "<> now()" Then MsgBox ("Future dates not allowed!")
Set r = Sheet21.range("C2")
If r.Value = "<> now()" Then MsgBox ("Future dates not allowed!")
Yes. I'll try it myself tomorrow when in front of excel.
Hi leezac,
I think the second if-statement should be as follows:
I think the second if-statement should be as follows:
If r.Value > Now() Then MsgBox ("Future dates not allowed!")
But the error occurs at the following line, am I correct?If Not Intersect(Target, range("C2")) Is Nothing Then
Have you set Target as a range?
ASKER
I will look at tomorrow.
ASKER
JazzyJoop - yes error is on second line.
What is Target? Is it set as a range?
ASKER
Not sure what Target means, but added Set target and the code seems to work. What would be right way though?
Set ws = Application.ActiveSheet
Set r = Sheet21.Range("C2")
Set target = Sheet21.Range("C2")
If Intersect(target, Range("C2")) > 0 Then
'If Not (Sheet21.Range("C2")) Is Nothing Then
If r.Value > Now() Then MsgBox ("Future dates not allowed!")
End If
Set ws = Application.ActiveSheet
Set r = Sheet21.Range("C2")
Set target = Sheet21.Range("C2")
If Intersect(target, Range("C2")) > 0 Then
'If Not (Sheet21.Range("C2")) Is Nothing Then
If r.Value > Now() Then MsgBox ("Future dates not allowed!")
End If
ASKER
Another problem is the date in C2 is 20130605 format not in date
I tried changing
If r.Value <> Format(Now(), "yyyymmdd") Then MsgBox ("File not today'
but does not work.
I tried changing
If r.Value <> Format(Now(), "yyyymmdd") Then MsgBox ("File not today'
but does not work.
Intersect returns a range object that represents the intersection of (in this case) two ranges.
I'm getting the idea that you're not needing this particular method?
If you set target as C2, then you're checking of C2 intersects with C2, which it does, but seems unnecessary.
If you're only interested in testing if the date in C2 is not a future date, you can leave this part out:
I'm getting the idea that you're not needing this particular method?
If you set target as C2, then you're checking of C2 intersects with C2, which it does, but seems unnecessary.
If you're only interested in testing if the date in C2 is not a future date, you can leave this part out:
Set target = Sheet21.Range("C2")
If Intersect(target, Range("C2")) > 0 Then
End if
ASKER
I need to check that the date is not todays date
There are probably better ways to convert that string into a date, but the following works:
Left(r, 4) returns the year
Mid(r, 5, 2) returns the month
Mid(r, 7, 2) returns the day
DateValue converts it into a date
This assumes that months and days are always a two character number.
Set r = Sheet21.Range("C2")
If DateSerial(Left(r, 4), Mid(r, 5, 2), Mid(r, 7, 2)) <> DateValue(Now) Then MsgBox ("File not today")
Left(r, 4) returns the year
Mid(r, 5, 2) returns the month
Mid(r, 7, 2) returns the day
DateValue converts it into a date
This assumes that months and days are always a two character number.
ASKER
=DATE(LEFT(C2,4),MID(C2,5, 2),RIGHT(C 2,2))
I am using this to convert the date issue
I am using this to convert the date issue
It's the same approach, yours is a worksheet function. Mine is the vba variant.
ASKER
Yes - thanks for date code. I did not see before my last post
This is what I am ending up with
Set ws = Application.ActiveSheet
Set r = Sheet21.Range("C2")
If DateSerial(Left(r, 4), Mid(r, 5, 2), Mid(r, 7, 2)) <> DateValue(Now) Then MsgBox ("File not today")
This is what I am ending up with
Set ws = Application.ActiveSheet
Set r = Sheet21.Range("C2")
If DateSerial(Left(r, 4), Mid(r, 5, 2), Mid(r, 7, 2)) <> DateValue(Now) Then MsgBox ("File not today")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks - good to go.
Also, try today(). Definitely works in word, not sure about excel.