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

__._,_.___

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

Messages in this topic (3)

Recent Activity:

• New Members 13

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

.

__,_._,___