Howdee all.
Well, it appears my last post never got any response, so I'll modify it to see if that helps. I did however figure out that particular issue a couple of weeks later.
I'm using autofilter to compare data on two worksheets.
I've found that there are instances where my filter will jump to text filter if it cannot find the exact text being sought from my criteria option.
In general this causes the issue of the macro not accomplishing my goal of comparing numeric values based on other criteria. As such, I've now decided that I'd like to modify the macro to do a more generalized comparison-- using: if .... like.... then.
Please see my post from March 5, 2009 for the code.
Autofilter, multiselect, 2007
My goal is to have the filter look at a column's values, based on a worksheet's name (as my criteria). If the value in a given cell is like the sheet's name, then I want that to be my filtered value.
Presently, Autofilter will only select the exact match for that value, and if it cannot locate the exact value, it will jump to text filter.
Here is the present configuration that I do have working.
Code:
Sub FilterSteveA()
Dim fname As String
Dim wks As Worksheet
'ok, this macro is still not working correctly.
'for some reason that I'm unable to identify it selects a text filter instead of
'the name in the list below text filter.
'One thing that comes to mind is that since I'm using the name as a filter criteria,
' the names are in fact different-- because what the permit database can handle differs from
' what the print outs from the excel spreadsheets can handle.
'Something to consider.......
fname = ActiveSheet.Name
fname = Right(fname, 3)
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.
'mv2 = fname
For Each wks In ActiveWorkbook.Worksheets
If LCase(Left(wks.Name, 3)) Like "sum" Then
With wks
Select Case fname
Case "76a", "187", "718"
Sheets(wks.Name).Range("A15:g15").AutoFilter field:=2, Criteria1:=fname 'this is to take into account the claim number
End Select
Sheets(wks.Name).Range("A15:g15").AutoFilter field:=1, Criteria1:=mv ', this takes in to acct the chg appl# for a filter.
Sheets(wks.Name).Range("A15:g15").AutoFilter field:=4, Criteria1:=mv1 ', 'this takes in to account the owner name for a filter.
End With
End If
Next wks
End Sub
As you can see, the criteria will set a filter and look at specified columns. Then it looks at the criteria, and looks that up on my "sum" sheet. If there's not an exact match, it sets the text filter.
I'd like to use the if .... like..... then to check against.
Thank you in advance for your helps.....
SteveB.
AutoFilter
