Error Handler displaying message when no error

[Code Numpty]

I have the macro below to to select rows based on a start date and an end
date in column A. I hit problems if the selected dates are not on the
worksheet. I need the selection to include all dates within those selected.
With JLGWhiz's help I am trying to use an error handler but don't understand
what is happening.

Macro code is--------------------------------------------
Sub selDate()
Dim lstRw As Long, ws As Worksheet
Dim r1 As Range, r2 As Range, rng As Range
Dim x As String, y As String

Set ws = ActiveSheet
lstRw = ws.Cells(Rows.Count, 1).End(xlUp).Row
firstDt = InputBox("Enter beginning date", "START DATE")
secndDt = InputBox("Enter ending date", "END DATE")
Set rng = ws.Range("A2:A" & lstRw)
Set r1 = rng.Find(firstDt, LookIn:=xlValues)
Set r2 = rng.Find(secndDt, After:=Range("A2"), SearchDirection:=xlPrevious,
LookIn:=xlValues)
If Not r1 Is Nothing Then
x = r1.Address
Else:
GoTo ErrHandler
End If
If Not r2 Is Nothing Then
y = r2.Address
Else
GoTo ErrHandler
End If
ws.Range(x & ":" & y).EntireRow.Select
ErrHandler:
MsgBox "You have entered an invalid date. Start over", , "INVALID DATE"
End Sub
------------------------------------------------------------

Sample Data (dates are formatted as dates not text)

30/9/09 Wed 5/10/09 Mon 9 Sheldrake
30/9/09 Wed 3/10/09 Sat 15 Moffatt
30/9/09 Wed 15/10/09 Thu 21 Barratt
1/10/09 Thu 19/10/09 Mon 2 Hill
1/10/09 Thu 12/10/09 Mon 6 Hutchings
1/10/09 Thu 9/10/09 Fri 7 Hayward
1/10/09 Thu 23/10/09 Fri 34 Gates
2/10/09 Fri 6/10/09 Tue 10 Mais
2/10/09 Fri 12/10/09 Mon 14 Nelson
2/10/09 Fri 10/10/09 Sat 24 Price
2/10/09 Fri 5/10/09 Mon 32 Shore
5/10/09 Mon 9/10/09 Fri 4 Chandler
5/10/09 Mon 10/10/09 Sat 8 Griffiths
5/10/09 Mon 15/10/09 Thu 15 Fisher
5/10/09 Mon 14/10/09 Wed 23 Fletcher
5/10/09 Mon 22/10/09 Thu 30 Ivy
6/10/09 Tue 10/10/09 Sat 35 Ager
7/10/09 Wed 12/10/09 Mon 5 Myles
------------------------------------------------------------

If I enter START DATE as 1/10/09 and END DATE as 7/10/09 I get the error
message even though both dates are present in the worksheet and the correct
rows are highlighted

If I enter START DATE as 1/10/09 and END DATE as 4/10/09 I get the error
message as expected, although it would help if the date that was invalid were
specified.

 

[Per Jessen]

Hi

You need an 'Exit Sub' statement before the ErrHandler label.

Sub selDate()

'--- CUT---
ws.Range(x & ":" & y).EntireRow.Select
Exit Sub
ErrHandler:
MsgBox "You have entered an invalid date. Start over", , "INVALID DATE"
End Sub

Regards,
Per

 

[Mike H]

Hi,

Try this. note 2 changes Cdate(FirstDt) and the exit syb before errorhandler

Sub selDate()
Dim lstRw As Long, ws As Worksheet
Dim r1 As Range, r2 As Range, rng As Range
Dim x As String, y As String

Set ws = ActiveSheet
lstRw = ws.Cells(Rows.Count, 1).End(xlUp).Row
firstDt = InputBox("Enter beginning date", "START DATE")
secndDt = InputBox("Enter ending date", "END DATE")
Set rng = ws.Range("A2:A" & lstRw)
Set r1 = rng.Find(CDate(firstDt), LookIn:=xlValues)
Set r2 = rng.Find(CDate(secndDt), After:=Range("A2"),
SearchDirection:=xlPrevious, LookIn:=xlValues)

If Not r1 Is Nothing Then
x = r1.Address
Else:
GoTo ErrHandler
End If
If Not r2 Is Nothing Then
y = r2.Address
Else
GoTo ErrHandler
End If
ws.Range(x & ":" & y).EntireRow.Select
Exit Sub
ErrHandler:
MsgBox "You have entered an invalid date. Start over", , "INVALID DATE"
End Sub


Mike

 

[Code Numpty]

Thanks Mike. Per's earlier suggestion worked fine but I wanted to ask you
what the Cdate(FirstDt) does?

 

[Mike H]

Your inputbix is returning a string "1/10/2009"

Cdate - convert to date, converts it to 1/10/2009

Mike

 

[Code Numpty]

Thanks Mike. I'll make that change too to make it all as good as possible