Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date range vba code
New to vba. Trying to do a basic date range. If calendar
date is between start date and end date then display date matches if not then display date doesn't match. I know its something really simple. Please help. Sub Macro2() Dim calendar_dte, start_dte, end_dte As Integer calendar_dte = Range("E1").Select 'calendar date start_dte = Range("D1").Select 'Start Date end_dte = Range("D2").Select 'End Date If (calendar_dte = start_dte And calendar_dte <= end_dte) Then MsgBox "Calendar Date Matches Vacation Range" Else MsgBox "Calendar Date Does Not Match Vacation Range" End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date range vba code
A few problems.
1. Your Dim statement sets the first 2 variables as variants, the 3rd as an integer. 2. Dates are stored as number, but current dates are too large to fit in an integer variable. A Long (integer) would work, not not Integer. 3. Selecting a cell does just that. If you want to set a range variable to point to that cell, you would write Set Cal_Date = Range("E1") without the .Select and with Set. If you wrote it that way, the first 2 statements would compile, but not the one that uses End_Date. But that isn't what you want in any case. You want to transfer the value from a cell into a variable, not select the cell. You do that by setting the variable equal to the .Value property of the cell, like this: Sub Macro2 Dim Cal_Date As Date Dim Start_Date As Date Dim End_Date As Date Cal_Date = Range("E1").Value Start_Date = Range("D1").Value End_Date = Range("D2").Value If Cal_Date = Start_Date and Cal_Date <= End_Date Then Msgbox ..... End If End Sub On Mon, 27 Sep 2004 21:12:55 -0700, "PJ" wrote: New to vba. Trying to do a basic date range. If calendar date is between start date and end date then display date matches if not then display date doesn't match. I know its something really simple. Please help. Sub Macro2() Dim calendar_dte, start_dte, end_dte As Integer calendar_dte = Range("E1").Select 'calendar date start_dte = Range("D1").Select 'Start Date end_dte = Range("D2").Select 'End Date If (calendar_dte = start_dte And calendar_dte <= end_dte) Then MsgBox "Calendar Date Matches Vacation Range" Else MsgBox "Calendar Date Does Not Match Vacation Range" End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
date range vba code
On Tue, 28 Sep 2004 00:10:59 -0500, Myrna Larson
wrote in microsoft.public.excel.programming: [snip] But that isn't what you want in any case. You want to transfer the value from a cell into a variable, not select the cell. You do that by setting the variable equal to the .Value property of the cell, like this: [snip] Cal_Date = Range("E1").Value Start_Date = Range("D1").Value End_Date = Range("D2").Value .... or simply: Cal_Date = [E1] Start_Date = [D1] End_date = [D2] [snip] -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
date range vba code
I've heard others say that the bracket notation is very slow. I never use it.
On Tue, 28 Sep 2004 06:52:20 GMT, Michael Bednarek ) wrote: On Tue, 28 Sep 2004 00:10:59 -0500, Myrna Larson wrote in microsoft.public.excel.programming: [snip] But that isn't what you want in any case. You want to transfer the value from a cell into a variable, not select the cell. You do that by setting the variable equal to the .Value property of the cell, like this: [snip] Cal_Date = Range("E1").Value Start_Date = Range("D1").Value End_Date = Range("D2").Value ... or simply: Cal_Date = [E1] Start_Date = [D1] End_date = [D2] [snip] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
date range vba code
Thanks guys for all your help.
-----Original Message----- I've heard others say that the bracket notation is very slow. I never use it. On Tue, 28 Sep 2004 06:52:20 GMT, Michael Bednarek <ROT13 ) wrote: On Tue, 28 Sep 2004 00:10:59 -0500, Myrna Larson wrote in microsoft.public.excel.programming: [snip] But that isn't what you want in any case. You want to transfer the value from a cell into a variable, not select the cell. You do that by setting the variable equal to the .Value property of the cell, like this: [snip] Cal_Date = Range("E1").Value Start_Date = Range("D1").Value End_Date = Range("D2").Value ... or simply: Cal_Date = [E1] Start_Date = [D1] End_date = [D2] [snip] . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
date range vba code
One of those others is Microsoft.
-- Regards, Tom Ogilvy "Myrna Larson" wrote in message ... I've heard others say that the bracket notation is very slow. I never use it. On Tue, 28 Sep 2004 06:52:20 GMT, Michael Bednarek ) wrote: On Tue, 28 Sep 2004 00:10:59 -0500, Myrna Larson wrote in microsoft.public.excel.programming: [snip] But that isn't what you want in any case. You want to transfer the value from a cell into a variable, not select the cell. You do that by setting the variable equal to the .Value property of the cell, like this: [snip] Cal_Date = Range("E1").Value Start_Date = Range("D1").Value End_Date = Range("D2").Value ... or simply: Cal_Date = [E1] Start_Date = [D1] End_date = [D2] [snip] |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
date range vba code
Thanks for the confirmation, Tom.
On Tue, 28 Sep 2004 22:28:34 -0400, "Tom Ogilvy" wrote: One of those others is Microsoft. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Convert a julian gregorian date code into a regular date | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
VB Code Naming a Range (range changes each time) | Excel Programming | |||
code pasting a date changes date format in current month only | Excel Programming |