If you're always applying the filter arrows to A:F (6 columns) and you're always
looking in the first column of that range (column A in this situation), then the
field would be 1.

But if your data had names in column A, cities in column B, states in column C,
zips in column D and you wanted to filter columns A:F by Date (column D), then
the field would be 4.

Your code could determine what that field number is -- if you know the rules to
apply.

On the other hand, if your names were sometimes in column A, sometimes in column
D and sometimes in column F, then you're going to have trouble--I would think
that you'd want to filter each of those fields and extra whatever info you
could.

(Actually, I'd do my best to make the data nice. Clean it up before your macro
even sees it. Yep, this is a miserable task.)

=======
It sounds like you may be filtering by all the unique values in a field (or
multiple fields).

Instead of looping through those unique values, you may want to look at
pivottables. You'll be able to get subtotals for each unique value pretty
quickly.



Steve wrote:

So, let me see if I'm grasping your point..... the field element would be
constant-- period-- always the numeric value of the column (field that I want
to be viewed for my criteria)?
And it's my criteria that'd be the variant? Ok, that's making sense to
me.....
To answer your question-- mv, and mv1 are the values associated with the
column-- hence the range element defined near the top of the macro. I.e.,

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

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

An explanation of the purpose for all this--

In my filter, I'm filtering two columns' values. I then activate the macro,
and it then filters a second, or destination worksheet for me. I have two
subtotal worksheet functions that then tell me the values of the filtered
elements. I then have an if worksheet function to tell me if those subtotal
functions match or not. It is indeed a lengthy routine, but it works.
The reason that I've done it this way is that my source page is a detailed
breakdown of all the elements, and the destination page is a summary of those
elements. Once I verify that both match, I then take the detailed page, and
copy/paste it into our sql DB for public access.

You should've seen the old way..... inaccurate, and extremely tedious.

"Dave Peterson" wrote:

I'm not sure what mv1 is, but you can use:

Criteria1:="*" & mv1 & "*"
for contains.

Criteria1:=mv1 & "*"
for starts with.

Criteria1:="*" & mv1
for ends with.

After that, I think you'll have to use pick apart that name (mid's, left's,
right's, instr's may help).

Steve wrote:

morning all.
I received a filter macro from here last October, 2008. Over the past 11
months or so, I've been modifying it as needed to meet various needs, and up
until now, all of my mod's have worked great.
I know I posted on this previously, but it was never answered due to my not
understanding what I was trying to explain.
The filter tool in excel looks for a specific statement based on the
criteria selected.
For my need, there are times when I want to look at a name that's LIKE the
criteria-- but not necessary the same, EXACTLY spelled name. Say, there are
names that are missing periods, for middle initials, or the date in one use
is configured mm/dd/yy, and another is mm/dd/yyyy, or Month-Day-Year, or
have an ampersand instead of and, etc.....
Presently, my filter ignores those-- as it should, I suppose-- and if it
can't find the exact match, there is no match.

My code is:

Sub FilterA()

Dim wks As Worksheet

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

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

For Each wks In ActiveWorkbook.Worksheets

If LCase(Left(wks.Name, 3)) Like "sum" Then

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 If

Next wks
End Sub

My goal would be to do something like--
if mv1 like field then
...........
end if

I've tried a few ideas I had, and none of them worked. And it's been several
weeks since I tried them, so I can't remember specifics at this point.

Thank you for your helps.
Best.

--

Dave Peterson
.


--

Dave Peterson