File is in the
Files\Code Sample Files
folder.
http://tech.groups.yahoo.com/group/ExcelVBA/files/Code%20Sample%20Files/SampleDeleteMoYrFromRondeBruinCodeMod.xlsm
--- In ExcelVBA@yahoogroups.com, "sspatriots" <sspatriots@...> wrote:
>
> Just mailed this last file that I made work to the moderator for posting. I think it is only recursive until the user gets their entry right or chooses to cancel out. Have a look if you get to see it. Thanks, Steve
>
> --- In ExcelVBA@yahoogroups.com, Gary Morris <gwmorris@> wrote:
> >
> > Hmm, that sounds a lot like a recursive approach, when a sub or
> > function calls itself over and over to get a job done. At this point,
> > Excel keeps adding and deprecating things in the VBA dept.,
> > making it difficult to keep up at best. Recursive programming is a
> > powerful tool for the right task, and I'm not sure if you even need
> > the "Call" anymore. If you could ever upload the workbook, we
> > might be able to see it a bit clearer.
> >
> > Â
> > G.
> >
> >
> > ________________________________
> > From: sspatriots <sspatriots@>
> > To: ExcelVBA@yahoogroups.com
> > Sent: Friday, March 16, 2012 9:08 AM
> > Subject: [ExcelVBA] Re: Filter Choice Macro
> >
> >
> > Â
> >
> >
> > Hi Dave,
> >
> > I have found something by Ron de Bruin called 'Delete_with_Autofilter_Two_Criteria' that seems to be doing what I want, except that I have modified it a bit to add the input boxes and catch the Errors with a vbYes/No option. VbNo button will take me out and vbYes button calls the macro to start over (because I don't want kick the user out of it if they make a mistake or what they are trying to filter isn't in the list). I will send the before workbook (the one I tried to post the code for)to the group later as well as what I have put together.
> >
> > Now my question. Is it acceptable to use the 'Call ....' command to have a macro to call itself in the error handling exception with a vbYes response by the user? Pardon me if I'm not using the correct excel syntax here, always learning...
> >
> > Regards,
> >
> > Steve
> >
> > --- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@> wrote:
> > >
> > > [Hmmmf ... I wrote this then left it in my drafts folder all day. I think
> > > it's still relevant though.]
> > >
> > > I seem to remember that the moderators have limited this group so that they,
> > > only, can upload files. Send your file to the moderators using the
> > > ExcelVBA-owner@yahoogroups.com e-mail address.
> > >
> > > Moderators, if this is correct, would you mind updating the home page
> > > information to say as such, please.
> > >
> > > SSPatriots, I have read through your code and can see what it's trying to
> > > do. I can't see anything in it that would cause line 40 to fail, though,
> > > and would need to run it in debug mode to see what's going wrong. So you
> > > definitely need to upload the file.
> > >
> > > However, part of your code is
> > >
> > > > 40 Range(Cells(2, 1), Cells(ActiveSheet.UsedRange.Rows.Count,
> > > > 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
> > > >
> > > > Selection.AutoFilter
> > > >
> > > >
> > > > Error:
> > >
> > > Which means that a SUCCESSFUL delete at 40 will then fall into the error
> > > code and produce the error message. But this would happen EVERY TIME, not
> > > just second time around.
> > >
> > > As Derek has pointed out, you need an Exit Sub before the Error code.
> > >
> > > I'd also suggest not looping back after you get an error. Simply let the
> > > sub finish and then run it again. After all, if you don't get an error, it
> > > will stop and you will then need to run it again to delete another month.
> > >
> > > Regards, Dave S
> > >
> > > ----- Original Message -----
> > > From: "sspatriots" <sspatriots@>
> > > To: <ExcelVBA@yahoogroups.com>
> > > Sent: Friday, March 16, 2012 5:55 AM
> > > 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
> > > >> >
> > > >>
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > ----------------------------------
> > > > 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
> > > >
> > >
> >
> >
> >
> >
> > [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