morning all.
I have a macro that I obtained from Tom Hutchings last year, and it just
recently began selecting the text filter, instead of the drop down list
normally selected.
My question is twofold---
1- Why? So I can stop it from continuing to happen.
2-How can I get it to just select the items I specify? (what it was doing
before)

Code as follows.

Sub Filter()

Dim wks As Worksheet

mv = range("f3").End(xlDown).value
'this sets the criteria for the ChgAppl#.

mv1 = range("a3").End(xlDown).value
'this is my add-on to set a second criteria filter- Name of owner.

For Each wks In ActiveWorkbook.Worksheets

Select Case wks.Name

Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ",
"SUMMARY", "SUMMARY ", "SUM189", " SUM189", "SUM189 "
With wks
Sheets(wks.Name).range("A8:F8").AutoFilter field:=1, Criteria1:=mv
'this takes in to acct the chg appl# for a filter.

Sheets(wks.Name).range("A8:F8").AutoFilter field:=3, Criteria1:=mv1
'this takes in to account the owner name for a filter.

End With
End Select

Next wks


End Sub

Thank you.

As an addendum,

Sheets(wks.Name).range("A8:F8").AutoFilter field:=1, Criteria1:=mv
mv = range("f3").End(xlDown).value


the field for this one is what's selecting the text filter, instead of the
list of elements that are normally selected. (I.e., once you set the filter,
you have a choice of options. Sort Alpha-forward/reverse, sort or filter by
Color, Clear filtered, text filter, and a list of field contents.)

I need the field contents option always in this routine.

It works fine when it's selecting a numeric value for my criteria. But when
I set my criteria as "Blanks" it jumps to the text filter.

Then, when I went to look at the text filter setting, it'd selected a name,
instead of a blank value-- as I would've anticipated. I.e., at the bottom of
the field contents, there's generally a "Blanks" selection if there are
blanks in your selection for filtering-- and in my specific case, there's
always a "Blanks" option in the field contents.



"SteveDB1" wrote:

morning all.
I have a macro that I obtained from Tom Hutchings last year, and it just
recently began selecting the text filter, instead of the drop down list
normally selected.

As a secondary addendum,

In looking more closely at the filter, I see that it has treated my criteria
as a word from another column, not related to my range selection.

I.e., my criteria should come out of column F, row 3, and look down.
The word it's using as my criteria is being pulled from column C, row 2.

Then, on another note, I've looked in the help file to see if there was
something I could use to make certain it did not select the text filter. All
I found was Operator. I'd hoped that the options within Operator would allow
for "Blanks" but it doesn't.
The closest I could get was "values."

Any ideas at this point would be appreciated.
Thank you.




"SteveDB1" wrote:

morning all.
I have a macro that I obtained from Tom Hutchings last year, and it just
recently began selecting the text filter, instead of the drop down list
normally selected.
My question is twofold---
1- Why? So I can stop it from continuing to happen.
2-How can I get it to just select the items I specify? (what it was doing
before)

Code as follows.

Sub Filter()

Dim wks As Worksheet

mv = range("f3").End(xlDown).value
'this sets the criteria for the ChgAppl#.

mv1 = range("a3").End(xlDown).value
'this is my add-on to set a second criteria filter- Name of owner.

For Each wks In ActiveWorkbook.Worksheets

Select Case wks.Name

Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ",
"SUMMARY", "SUMMARY ", "SUM189", " SUM189", "SUM189 "
With wks
Sheets(wks.Name).range("A8:F8").AutoFilter field:=1, Criteria1:=mv
'this takes in to acct the chg appl# for a filter.

Sheets(wks.Name).range("A8:F8").AutoFilter field:=3, Criteria1:=mv1
'this takes in to account the owner name for a filter.

End With
End Select

Next wks


End Sub

Thank you.

For what it's worth, I finally figured out what my filter problem was.
The issue started when I'd created a test utility for verification on my
compound ownerships.
I'd placed a series of equations down about 20 rows below the end of my
data. As a title for one of my columns I'd given it a name. That column
matched my primary filtering criteria, and was being caught by the filter to
make it a text filter instead of a numeric selection filter.
So, I found that by moving my test utility over one column to the right--
outside the domain of my filter's criteria column it resolved my issue, and
I'm now back in business with my filter.
Go figure that things could get so sticky......