date range vba code

[PJ]

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

 

[Myrna Larson]

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

 

[Michael Bednarek]

[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]

 

[Myrna Larson]

I've heard others say that the bracket notation is very slow. I never use it.

[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]

 

[PJ]

Thanks guys for all your help.

-----Original Message-----
I've heard others say that the bracket notation is very slow. I never use it.

[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]

.

 

[Tom Ogilvy]

One of those others is Microsoft.

--
Regards,
Tom Ogilvy

I've heard others say that the bracket notation is very slow. I never use it.

[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]

 

[Myrna Larson]

Thanks for the confirmation, Tom.