| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA help
I'm trying to amend a simple macro by copy/paste and amending the VBA.
The macro runs until it gets to my first new line, the gives a message " Run-time error 1004 AutoFilter Method of Range class failed". The line it stops at is :- Selection.AutoFilter Field:=7, Criteria1:="15", Operator:=xlTop10Percent ( I want it to autofilter and get the top 15% in that column) Any ideas what I'm doing wrong ? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA help
Main suspect is that your code (or its user) selects and activates various
stuff and ends up away from your data. Try Sheets("Sheet1").Range("G2").CurrentRegion.AutoFil ter _ Field:=7, Criteria1:="15", Operator:=xlTop10Percent HTH. Best wishes Harald "Rich" skrev i melding ... I'm trying to amend a simple macro by copy/paste and amending the VBA. The macro runs until it gets to my first new line, the gives a message " Run-time error 1004 AutoFilter Method of Range class failed". The line it stops at is :- Selection.AutoFilter Field:=7, Criteria1:="15", Operator:=xlTop10Percent ( I want it to autofilter and get the top 15% in that column) Any ideas what I'm doing wrong ? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA help
Thanks Harald, That too gives the same error, here's the full sub:-
Sub yellows() ' ' yellows Macro ' Macro recorded 30/07/2006 by davier05 ' ' Keyboard Shortcut: Ctrl+y ' Selection.AutoFilter Field:=1, Criteria1:="AFD617" Selection.AutoFilter Field:=11, Criteria1:="500", Operator:=xlAnd Selection.AutoFilter Field:=3, Criteria1:="15", Operator:=xlBottom10Percent Range("D6:D19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=3 Selection.AutoFilter Field:=11 Selection.AutoFilter Field:=5, Criteria1:="99", Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:="15", Operator:=xlTop10Percent Range("E6:E19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=4 Selection.AutoFilter Field:=5 Selection.AutoFilter Field:=11, Criteria1:="500", Operator:=xlAnd Selection.AutoFilter Field:=6, Criteria1:="15", Operator:=xlTop10Percent Range("G6:G19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=6 Selection.AutoFilter Field:=7, Criteria1:="15", Operator:=xlTop10Percent Range("H6:H19819").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=7 Selection.AutoFilter Field:=8, Criteria1:="15", Operator:=xlTop10Percent Range("I6:I19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=8 Selection.AutoFilter Field:=9, Criteria1:="15", Operator:=xlTop10Percent Range("J6:J19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=9 Selection.AutoFilter Field:=10, Criteria1:="15", Operator:=xlTop10Percent Range("K6:K19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=10 End Sub It get all the way down to the field 7 bit, before the error. "Harald Staff" wrote in message ... Main suspect is that your code (or its user) selects and activates various stuff and ends up away from your data. Try Sheets("Sheet1").Range("G2").CurrentRegion.AutoFil ter _ Field:=7, Criteria1:="15", Operator:=xlTop10Percent HTH. Best wishes Harald "Rich" skrev i melding ... I'm trying to amend a simple macro by copy/paste and amending the VBA. The macro runs until it gets to my first new line, the gives a message " Run-time error 1004 AutoFilter Method of Range class failed". The line it stops at is :- Selection.AutoFilter Field:=7, Criteria1:="15", Operator:=xlTop10Percent ( I want it to autofilter and get the top 15% in that column) Any ideas what I'm doing wrong ? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA help
Could it be that you have gotten to a position where there is nothing else
to filter? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rich" wrote in message ... Thanks Harald, That too gives the same error, here's the full sub:- Sub yellows() ' ' yellows Macro ' Macro recorded 30/07/2006 by davier05 ' ' Keyboard Shortcut: Ctrl+y ' Selection.AutoFilter Field:=1, Criteria1:="AFD617" Selection.AutoFilter Field:=11, Criteria1:="500", Operator:=xlAnd Selection.AutoFilter Field:=3, Criteria1:="15", Operator:=xlBottom10Percent Range("D6:D19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=3 Selection.AutoFilter Field:=11 Selection.AutoFilter Field:=5, Criteria1:="99", Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:="15", Operator:=xlTop10Percent Range("E6:E19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=4 Selection.AutoFilter Field:=5 Selection.AutoFilter Field:=11, Criteria1:="500", Operator:=xlAnd Selection.AutoFilter Field:=6, Criteria1:="15", Operator:=xlTop10Percent Range("G6:G19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=6 Selection.AutoFilter Field:=7, Criteria1:="15", Operator:=xlTop10Percent Range("H6:H19819").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=7 Selection.AutoFilter Field:=8, Criteria1:="15", Operator:=xlTop10Percent Range("I6:I19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=8 Selection.AutoFilter Field:=9, Criteria1:="15", Operator:=xlTop10Percent Range("J6:J19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=9 Selection.AutoFilter Field:=10, Criteria1:="15", Operator:=xlTop10Percent Range("K6:K19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=10 End Sub It get all the way down to the field 7 bit, before the error. "Harald Staff" wrote in message ... Main suspect is that your code (or its user) selects and activates various stuff and ends up away from your data. Try Sheets("Sheet1").Range("G2").CurrentRegion.AutoFil ter _ Field:=7, Criteria1:="15", Operator:=xlTop10Percent HTH. Best wishes Harald "Rich" skrev i melding ... I'm trying to amend a simple macro by copy/paste and amending the VBA. The macro runs until it gets to my first new line, the gives a message " Run-time error 1004 AutoFilter Method of Range class failed". The line it stops at is :- Selection.AutoFilter Field:=7, Criteria1:="15", Operator:=xlTop10Percent ( I want it to autofilter and get the top 15% in that column) Any ideas what I'm doing wrong ? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA help
Hi Bob,
No there is definitely data to filter. I am stuck ! Rich " Could it be that you have gotten to a position where there is nothing else to filter? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rich" wrote in message ... Thanks Harald, That too gives the same error, here's the full sub:- Sub yellows() ' ' yellows Macro ' Macro recorded 30/07/2006 by davier05 ' ' Keyboard Shortcut: Ctrl+y ' Selection.AutoFilter Field:=1, Criteria1:="AFD617" Selection.AutoFilter Field:=11, Criteria1:="500", Operator:=xlAnd Selection.AutoFilter Field:=3, Criteria1:="15", Operator:=xlBottom10Percent Range("D6:D19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=3 Selection.AutoFilter Field:=11 Selection.AutoFilter Field:=5, Criteria1:="99", Operator:=xlAnd Selection.AutoFilter Field:=4, Criteria1:="15", Operator:=xlTop10Percent Range("E6:E19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=4 Selection.AutoFilter Field:=5 Selection.AutoFilter Field:=11, Criteria1:="500", Operator:=xlAnd Selection.AutoFilter Field:=6, Criteria1:="15", Operator:=xlTop10Percent Range("G6:G19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=6 Selection.AutoFilter Field:=7, Criteria1:="15", Operator:=xlTop10Percent Range("H6:H19819").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=7 Selection.AutoFilter Field:=8, Criteria1:="15", Operator:=xlTop10Percent Range("I6:I19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=8 Selection.AutoFilter Field:=9, Criteria1:="15", Operator:=xlTop10Percent Range("J6:J19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=9 Selection.AutoFilter Field:=10, Criteria1:="15", Operator:=xlTop10Percent Range("K6:K19820").Select Selection.Interior.ColorIndex = 6 Selection.AutoFilter Field:=10 End Sub It get all the way down to the field 7 bit, before the error. "Harald Staff" wrote in message ... Main suspect is that your code (or its user) selects and activates various stuff and ends up away from your data. Try Sheets("Sheet1").Range("G2").CurrentRegion.AutoFil ter _ Field:=7, Criteria1:="15", Operator:=xlTop10Percent HTH. Best wishes Harald "Rich" skrev i melding ... I'm trying to amend a simple macro by copy/paste and amending the VBA. The macro runs until it gets to my first new line, the gives a message " Run-time error 1004 AutoFilter Method of Range class failed". The line it stops at is :- Selection.AutoFilter Field:=7, Criteria1:="15", Operator:=xlTop10Percent ( I want it to autofilter and get the top 15% in that column) Any ideas what I'm doing wrong ? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA help
I've just noticed that when I try to filter that column by top 15% manually
using the autofilter, it doesn't work either !! Rich "Rich" wrote in message ... I'm trying to amend a simple macro by copy/paste and amending the VBA. The macro runs until it gets to my first new line, the gives a message " Run-time error 1004 AutoFilter Method of Range class failed". The line it stops at is :- Selection.AutoFilter Field:=7, Criteria1:="15", Operator:=xlTop10Percent ( I want it to autofilter and get the top 15% in that column) Any ideas what I'm doing wrong ? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA help
The code is fine then. Problem is elsewhere. But I advice you to get rid of
the Select and Selection stuff anyway. Best wishes Harald "Rich" skrev i melding ... I've just noticed that when I try to filter that column by top 15% manually using the autofilter, it doesn't work either !! Rich |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
