PDA

View Full Version : Select Case using date ranges



wolf.stalker
11-12-2008, 06:29 AM
hi all. i am stuck.

i am reading in a value from sheet1.range("A1") and that value is "12/22/2008" (text...not number formatted).

my case statement is similar to this.

select case sheet1.range("A1")

case "12/01/2008" to "1/1/2009"
' do stuff

end select

when this runs, it will always skip my ranged date even though it falls in it. is this because we are comparing the input string to the range'd string?

i am just trying to understand why i am not able to use my case statement with a date range. any advice would be helpful. my next approach is going to be to maybe try converting to julian calander?

anyone have a good way of doing this?

Bob Phillips
11-12-2008, 06:42 AM
Because it isn't dates as you said. If it isn't numbers, it won't do a number comparison.

Paul_Hossler
11-12-2008, 10:41 AM
I put ="12/25/2008" in A1

and


Sub test()
Select Case Range("A1").Value

Case #1/1/2007# To #12/31/2007#
MsgBox 2007
Case #1/1/2008# To #12/31/2008#
MsgBox 2008
Case #1/1/2009# To #12/31/2009#
MsgBox 2009

End Select

End Sub



Seems to work as expected (the # ...# defines them as dates)

Paul

MaximS
11-12-2008, 10:50 AM
you my try this:


Sub date_comparsion()
Dim val, val1 As Date
val = DateSerial(Right(Range("A1"), 4), Mid(Range("A1"), 4, 2), Left(Range("A1"), 2))
val1 = DateSerial(Right(Range("A2"), 4), Mid(Range("A2"), 4, 2), Left(Range("A2"), 2))
If val > val1 Then
Range("B1").Value = "It's older"
Else
Range("B1").Value = "It's not older"
End If
End Sub

Cyberdude
11-12-2008, 05:35 PM
I'm not sure if this would help, but it struck me that oft times with text items I have to add a leading blank to the values, i.e., change #1/1/2007# to #b1/b1/2007# where the red "b" is my way of showing where a blank should go.
Just a thought.

wolf.stalker
11-12-2008, 07:31 PM
thanks all for your help....