Sorry, ignore my point about Exit Sub. Brainfade. Yes, it is definitely
needed.
Regards, Dave S
----- Original Message -----
From: "David Smart" <smartware.consulting@gmail.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Friday, March 16, 2012 8:17 AM
Subject: Re: [ExcelVBA] Re: Filter Choice Macro
> 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