Well.
Maybe putting 'Exit Sub' before the Error trap might help ?
However, this is not the real problem.
GoTo is considered harmful to code.
Derek +++
>________________________________
> From: sspatriots <sspatriots@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Thursday, 15 March 2012, 18:55
>Subject: [ExcelVBA] Re: Filter Choice Macro
>
>
>
>I tried uploading a file, however, it says the moderators aren't allowing file uploads. Of course, I may be doing something wrong. Any ideas?
>
>--- In ExcelVBA@yahoogroups.com, "Gary" <gwmorris@...> wrote:
>>
>> Whew!
>>
>> I've written a lot of Excel macros and functions since the 90's, but I've never seen anything like that! My suggestion would be to upload a copy of the spreadsheet (even if you need to change the data) and let us know exactly what you are doing here. I can barely make anything from the code, as I gave up GOTO many years ago, but I did at least try to piece it together. I really didn't even know you could still use GOTO (except for errors)! I'll help if I can though.
>>
>> --- In ExcelVBA@yahoogroups.com, "sspatriots" <sspatriots@> wrote:
>> >
>> > I have this macro that I've pieced together to filter on two columns. Column C is the numeric month and Column A is the numeric Year. The macro supposed to let the user enter a month first, then a year and have the resulting rows of filtered information deleted. This think works to catch invalid entries only to a point and I'm not sure what I have wrong.
>> >
>> > For example if I enter month 2 for the month and year 11 for the year and the spreadsheet doesn't contain any data for the year 2011, then I get a message box with an option to start over. If I take the start over option, the second time around the macro gets hung up at the line that starts with "40 Range(Cells(2, 1), Cells(ActiveSheet...". It gives me the "Run-time error '1004': ... No cells were found." message.
>> >
>> > Hopefully, someone can have a look and tell me what I have wrong here. Thanks, Steve
>> >
>> > My code is below:
>> >
>> > Sub FilterChoice()
>> > Dim Resp As Long
>> > Dim Ans1
>> > Dim Ans2
>> >
>> > 20 Ans1 = InputBox("Enter month to remove (Enter 1 thru 12.)")
>> > If Ans1 >= 1 And Ans1 <= 12 Then
>> >
>> >
>> > Selection.AutoFilter
>> > ActiveSheet.Range("$C$1:$C$10001").AutoFilter Field:=3, Criteria1:=Ans1
>> >
>> >
>> > GoTo 30
>> > Else
>> > If Ans1 <> "" Then
>> > MsgBox "Sorry you entered an invalid month number! Please enter a value between 1 and 12."
>> > GoTo 20
>> >
>> > End If
>> >
>> > End If
>> >
>> >
>> >
>> > 30 Ans2 = InputBox("Enter year to remove (Enter 00 thru 99.)")
>> > If Ans2 >= 0 And Ans2 <= 100 Then
>> >
>> >
>> > On Error GoTo Error
>> >
>> > ActiveSheet.Range("$A$1:$A$10001").AutoFilter Field:=1, Criteria1:=Ans2
>> >
>> >
>> >
>> > GoTo 40
>> >
>> >
>> >
>> > Else
>> > If Ans2 <> "" Then
>> > MsgBox "Sorry you entered an invalid year number! Please enter a value between 00 and 99."
>> > GoTo 30
>> > End If
>> >
>> > End If
>> >
>> >
>> >
>> >
>> > 40 Range(Cells(2, 1), Cells(ActiveSheet.UsedRange.Rows.Count, 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
>> >
>> > Selection.AutoFilter
>> >
>> >
>> > Error:
>> >
>> > Selection.AutoFilter
>> >
>> > Resp = MsgBox(prompt:="No data was found with the year that was provided. Try again?", Buttons:=vbYesNo)
>> >
>> > If Resp = vbYes Then
>> >
>> > GoTo 20
>> >
>> > Else
>> >
>> > End If
>> >
>> > If Resp = vbNo Then
>> >
>> > End If
>> >
>> > Exit Sub
>> >
>> >
>> >
>> > End Sub
>> >
>>
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar