Error Traceout in Advanced Filter [VBA]

[vijay.vizzu]

Dear All,


Below my code gives error while executing. Initially it works fine for me,but suddenly it starts giving error. I can't able to trace out the error. Please help me to resolve this error. If i will copy and paste the code in another sheet,it works but current workbook it not.

[pre]

Sub ApplyFilter()
Sheets("filters").Select
Range("b6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

Range("b6").Select
Worksheets("087(2GS1)").Range("fltRange").AdvancedFilter _
Action:=xlFilterCopy, _
criteriarange:=Worksheets("Filters").Range("fltCriteria"), _
copytorange:=Range("b6"), _
unique:=False
Range("b7").Select
MsgBox ("Total No. of Records: " & Range(Selection, Selection.End(xlDown)).Count)
End Sub

Sub Filter_087A()
Sheets("filters").Select
Range("b6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

Worksheets("087(2WS1)").Range("fltRange_087A").AdvancedFilter _
Action:=xlFilterCopy, _
criteriarange:=Worksheets("Filters").Range("fltCriteria"), _
copytorange:=Range("b6"), _
unique:=False
Range("b7").Select
MsgBox ("Total No. of Records: " & Range(Selection, Selection.End(xlDown)).Count)
End Sub

[/pre]
Run-time error '1004':


Application-defined or object-defined error


Regards

Vijay

 

[NARAYANK991]

Hi Vijay ,


Two points :


1. When you mention that you get a runtime error , please also mention the statement at which you get the error ; this will help.


2. You have posted two subroutines ; which of these is giving the error ? Or is it that both are giving the same error ?


Narayan

 

[vijay.vizzu]

Thank you so much NarayanK,

Sorry for the delay,

1. I am getting run time error, and it puts yellow color on advanced filter code. It gives error on doing advanced filter. If i will do manually by selecting advanced filter in data menu, it works fine, but with above code it not works

2. Both the subrouties are giving error.


so please help me to get rid of this.


thanks

Vijay

 

[Hui]

You may want to add this code up front to clear the existing filter and show all data first

[pre]

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If

[/pre]

 

[vijay.vizzu]

Dear Hui

Thank you for your comments, but this is not clearing my problem, still it gives error. If i will try this code in another sheet or doing manually by advanced filter it works fine.


Can't able to trace the problem. Please go in depth to trace the error, because you people are "Excel Ninjas" and i am not so familar with VBA


Thanks

Vijay

 

[Hui]

Any chance of uploading a sample file ?

 

[vijay.vizzu]

Dear Hui,


Can't able to upload the file, but if you will provide the email id then i can send it to you.

 

[NARAYANK991]

Hi Vijay ,


I don't think there is any problem.


I copied your code for the first procedure , and ran it on my file ; it executed without any error , and produced the correct results.


I had created two named ranges :


1. fltRange , referring to my data range : ='087(2GS1)'!$B$2:$H$26


This included the column headers.


2. fltCriteria , referring to the range : =filters!$P$1:$P$2


The first cell P1 contained the column header , and the second cell P2 contained the criterion.


Just BTW , slightly modify the MsgBox statement to :

[pre]

MsgBox ("Total No. of Records: " & IIf(Range("B7") = "", 0, Range(Selection, Selection.End(xlDown)).Count))

[/pre]
since , if there is no record which meets the criterion , you are informed that there are 1048570 records !


Narayan

 

[vijay.vizzu]

Dear Narayank/Hui,


I am extremely sorry for my foolishness. I hope you will forgive me. The problem is, in the above code, i have mentioned ranged name "fltCriteria" but it is actually named as Criteria not fltCriteria. So excel can't able to find criteria which is not exist in that workbook.


Finally, i have changed the named range as fltCriteria. Now it works fine.


Sorry for wastage of your valuable time

 

[vijay.vizzu]

Dear Narayank


Can you please explain "IIf" which is used in your msgbox statment.

 

[Hui]

IIF - The Immediate If function


It has the syntax:

IIf(Expression, TruePart, FalsePart)


so in your example

IIf(Range("B7") = "", 0, Range(Selection, Selection.End(xlDown)).Count))

if B7 = ""then 0 else Range(Selection, Selection.End(xlDown)).Count


To do that with a normal If would be

[pre]Msg = "Total No. of Records: "
If(Range("B7") = "" then
Msg = Msg + 0
else
Msg = Msg + Range(Selection, Selection.End(xlDown)).Count))
End If
MsgBox Msg

[/pre]