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@yahoo.com>
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