Error Handler displaying message when no error

  • Thread starter Thread starter Code Numpty
  • Start date Start date
C

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.
 
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
 
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
 
Thanks Mike. Per's earlier suggestion worked fine but I wanted to ask you
what the Cdate(FirstDt) does?
 
Your inputbix is returning a string "1/10/2009"

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

Mike
 
Back
Top