• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

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
0
leezac
Asked:
leezac
  • 10
  • 6
  • 2
1 Solution
 
Mark DamenERP System ManagerCommented:
Try r.value as you've already declared which cell r is.

Also, try today().  Definitely works in word, not sure about excel.
0
 
leezacAuthor Commented:
What if the date in the cell is formated like  20130606???
0
 
leezacAuthor Commented:
like this?

Set r = Sheet21.range("C2")

    If r.Value = "<> now()" Then MsgBox ("Future dates not allowed!")
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mark DamenERP System ManagerCommented:
Yes. I'll try it myself tomorrow when in front of excel.
0
 
MichaelBusiness AnalystCommented:
Hi leezac,

I think the second if-statement should be as follows:
If r.Value > Now() Then MsgBox ("Future dates not allowed!")

Open in new window

But the error occurs at the following line, am I correct?
If Not Intersect(Target, range("C2")) Is Nothing Then

Open in new window

Have you set Target as a range?
0
 
leezacAuthor Commented:
I will look at tomorrow.
0
 
leezacAuthor Commented:
JazzyJoop - yes error is on second line.
0
 
MichaelBusiness AnalystCommented:
What is Target? Is it set as a range?
0
 
leezacAuthor Commented:
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
0
 
leezacAuthor Commented:
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.
0
 
MichaelBusiness AnalystCommented:
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:
Set target = Sheet21.Range("C2")
If Intersect(target, Range("C2")) > 0 Then
End if

Open in new window

0
 
leezacAuthor Commented:
I need to check that the date is not todays date
0
 
MichaelBusiness AnalystCommented:
There are probably better ways to convert that string into a date, but the following works:

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")

Open in new window


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.
0
 
leezacAuthor Commented:
=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

I am using this to convert the date issue
0
 
MichaelBusiness AnalystCommented:
It's the same approach, yours is a worksheet function. Mine is the vba variant.
0
 
leezacAuthor Commented:
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")
0
 
MichaelBusiness AnalystCommented:
You can either use:
Set ws = Application.ActiveSheet
Set r = ws.Range("C2")
If DateSerial(Left(r, 4), Mid(r, 5, 2), Mid(r, 7, 2)) <> DateValue(Now) Then MsgBox ("File not today")

Open in new window

or
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")

Open in new window

If you want to read cell C2 from the active sheet, you can use the first.
If you want to read cell C2 from Sheet21 (not necessarily the active sheet), you can use the second.

In your code, as it is now, the following line has no function:
Set ws = Application.ActiveSheet
0
 
leezacAuthor Commented:
Thanks - good to go.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now