Comparing dates

[nectarinesupreme]

Hi, I'm trying to compare dates in a macro and I think I just need to
be the told the correct format. Currently I have

If (DateValue(testdate) >= DateValue("5 / 1 / 2007")) Then
cellSTBpaste = "Y" & ActiveCell.Row
cellorderpaste = "X" & ActiveCell.Row
Else
cellSTBpaste = "AB" & ActiveCell.Row
cellorderpaste = "AA" & ActiveCell.Row
End If

where testdate is a formatted date taken from a cell in the
spreadsheet. Could someone tell me the correct way to do it?

Thanks!

 

[Guest]

Is testdate a string or an actual dateformat (number). You also should
remove extra spaces If think you want
If (testdate >= DateValue("5/1/2007"))

 

[nectarinesupreme]

I've tried just the If (testdate >= DateValue("5/1/2007")) and I'm
still having the same problems. Everything is going through the If
part and nothing through the Else part.

 

[Guest]

Testtdate is not right. I can't tell the format of testdate

DateValue("5/1/2007") >= DateValue("5/8/2007"))
would execute tthe else statements

you can add this in the code to help
MsgBox (IsDate(DateValue(testdate)))
Msgbox(testdate)

 

[nectarinesupreme]

What's weird is if I change the >= to a <

If (testdate < DateValue("5/1/2007")) Then
cellSTBpaste = "Y" & ActiveCell.Row
cellorderpaste = "X" & ActiveCell.Row
Else
cellSTBpaste = "AB" & ActiveCell.Row
cellorderpaste = "AA" & ActiveCell.Row
End If

then everything goes through the Else and nothing through the If. Any
ideas?

 

[nectarinesupreme]

Thanks all, I figured it out. The problem was with testdate. New to
this stuff - thanks for the help!

 

[Guest]

That's not weird, that's logic.

If nothing was going through the ELSE with >=, then changing to < would make
everything go through the ELSE.

It is equivalent to doing

if 5 >= 3 then
'execute if true <= this will always be executed
else
'execute if false
end if

if 5 < 3 then
'execute if true
else
'execute if false <= this will always be executed

end if

 

[Rick Rothstein \(MVP - VB\)]

What's weird is if I change the >= to a <

If (testdate < DateValue("5/1/2007")) Then
cellSTBpaste = "Y" & ActiveCell.Row
cellorderpaste = "X" & ActiveCell.Row
Else
cellSTBpaste = "AB" & ActiveCell.Row
cellorderpaste = "AA" & ActiveCell.Row
End If

then everything goes through the Else and nothing through the If. Any
ideas?

Can you put a break point on this If statement (click the gray border on the
left of it to produce a red dot and highlight the line in red), then run
your macro and when it stops, issue a ?testdate command in the Immediate
window and tell us what prints out?

Rick