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