[ExcelVBA] Re: Filter Choice Macro

 

I did add the Exit Sub after I posted earlier, however, I still have the other problem with the runtime error message if the user makes a mistake the second time through. Thanks, Steve

--- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...> wrote:
>
> Sorry, ignore my point about Exit Sub. Brainfade. Yes, it is definitely
> needed.
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "David Smart" <smartware.consulting@...>
> 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@...>
> > 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@...>
> >>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
> >
> >
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (9)

Recent Activity:

• New Members 14

Visit Your Group

----------------------------------
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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___