No, goto isn't harmful to code ... it's harmful to humans. :-)
I don't think Exit Sub is the answer, as it would terminate the execution
completely. (Having said that, I don't know what the answer is, though.)
Regards, Dave S
----- Original Message -----
From: "Derek Turner" <g4swy@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Friday, March 16, 2012 6:22 AM
Subject: Re: [ExcelVBA] Re: Filter Choice Macro
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
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1424 / Virus Database: 2113/4871 - Release Date: 03/14/12
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