|
Error Handler displaying message when no error
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. |
Error Handler displaying message when no error
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 "Code Numpty" skrev i meddelelsen ... 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. |
Error Handler displaying message when no error
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" wrote: 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. |
Error Handler displaying message when no error
Thanks Mike. Per's earlier suggestion worked fine but I wanted to ask you
what the Cdate(FirstDt) does? "Mike H" wrote: 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" wrote: 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. |
Error Handler displaying message when no error
Your inputbix is returning a string "1/10/2009"
Cdate - convert to date, converts it to 1/10/2009 Mike "Code Numpty" wrote: Thanks Mike. Per's earlier suggestion worked fine but I wanted to ask you what the Cdate(FirstDt) does? "Mike H" wrote: 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" wrote: 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. |
Error Handler displaying message when no error
Thanks Mike. I'll make that change too to make it all as good as possible :-)
"Mike H" wrote: Your inputbix is returning a string "1/10/2009" Cdate - convert to date, converts it to 1/10/2009 Mike "Code Numpty" wrote: Thanks Mike. Per's earlier suggestion worked fine but I wanted to ask you what the Cdate(FirstDt) does? "Mike H" wrote: 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" wrote: 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. |
| All times are GMT +1. The time now is 01:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com