Solved

Checking for todays date

Posted on 2013-06-06
18
198 Views
Last Modified: 2013-06-11
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
Comment
Question by:leezac
  • 10
  • 6
  • 2
18 Comments
 
LVL 13

Expert Comment

by:markusdamenous
ID: 39227090
Try r.value as you've already declared which cell r is.

Also, try today().  Definitely works in word, not sure about excel.
0
 

Author Comment

by:leezac
ID: 39227182
What if the date in the cell is formated like  20130606???
0
 

Author Comment

by:leezac
ID: 39227187
like this?

Set r = Sheet21.range("C2")

    If r.Value = "<> now()" Then MsgBox ("Future dates not allowed!")
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:markusdamenous
ID: 39227348
Yes. I'll try it myself tomorrow when in front of excel.
0
 
LVL 6

Expert Comment

by:Michael
ID: 39233399
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
 

Author Comment

by:leezac
ID: 39233578
I will look at tomorrow.
0
 

Author Comment

by:leezac
ID: 39238011
JazzyJoop - yes error is on second line.
0
 
LVL 6

Expert Comment

by:Michael
ID: 39238028
What is Target? Is it set as a range?
0
 

Author Comment

by:leezac
ID: 39238052
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
 

Author Comment

by:leezac
ID: 39238101
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
 
LVL 6

Expert Comment

by:Michael
ID: 39238109
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
 

Author Comment

by:leezac
ID: 39238149
I need to check that the date is not todays date
0
 
LVL 6

Expert Comment

by:Michael
ID: 39238197
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
 

Author Comment

by:leezac
ID: 39238215
=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

I am using this to convert the date issue
0
 
LVL 6

Expert Comment

by:Michael
ID: 39238231
It's the same approach, yours is a worksheet function. Mine is the vba variant.
0
 

Author Comment

by:leezac
ID: 39238235
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
 
LVL 6

Accepted Solution

by:
Michael earned 335 total points
ID: 39238295
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
 

Author Closing Comment

by:leezac
ID: 39238776
Thanks - good to go.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question