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

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

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"

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]

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]

.

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]

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.