ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   VBA help (https://www.excelbanter.com/excel-discussion-misc-queries/102202-vba-help.html)

Rich
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 ?

Harald Staff
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 ?

Rich
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 ?

Bob Phillips
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 ?

Rich
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 ?

Rich
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 ?

Harald Staff
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


All times are GMT +1. The time now is 07:15 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com