VBA help

R

Rich

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

Harald Staff

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.AutoFilter _
Field:=7, Criteria1:="15", Operator:=xlTop10Percent

HTH. Best wishes Harald
 
R

Rich

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.
 
B

Bob Phillips

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)
 
R

Rich

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
 
R

Rich

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
 
H

Harald Staff

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
 
Top