PDA

View Full Version : Solved: AutoFilter Question



vzachin
01-30-2008, 12:31 PM
Hi,

I have the following code that does an autofilter in Column B and places the information into Column C but will give me an error message when the Criteria is blank. How can I bybass this error message?
I get a Run-time error '1004': No Cells were found.


iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Selection.AutoFilter Field:=2, Criteria1:="=*BROWN*", Operator:=xlAnd
Range("C5:C" & iLastRow & " ").SpecialCells(xlCellTypeVisible).Value = "BROWN"


thanks
zach

ProteanBeing
01-30-2008, 12:38 PM
look up On Error in the help files for a detailed description

Bob Phillips
01-30-2008, 01:23 PM
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Selection.AutoFilter Field:=2, Criteria1:="=*BROWN*", Operator:=xlAnd
On Error Resume Next
Set rng = Range("C5:C" & iLastRow & " ").SpecialCells(xlCellTypeVisible)
On Error Goto 0

If Not rng Is Nothing Then rng.Value = "BROWN"

vzachin
01-30-2008, 07:35 PM
EL XLD,

thanks again for your solution.
i added the following

Set rng = Nothing

after
On Error Resume Next
and that works


ProteanBeing:

thanks for the tip; sometimes i find the help files a little daunting

zach

Bob Phillips
01-31-2008, 01:17 AM
I assume that that means you are doing it within a loop?

vzachin
01-31-2008, 04:46 AM
hi xld,

i'm not doing this within a loop yet.
if i leave out

Set rng = Nothing
i get the following error message:
Run-time error '424':
Object required


wasn't sure how to fix this so i "read" some help files which i never seem to fully comprehend :banghead:


i will post a new question regarding how to loop through a list of criteria based on entries from a column in another sheet..


thanks again
zach