Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofilter macro
Morning all.
I'm working with an autofilter macro that I received from here last year. For the most part, it's been working really well, and has helped expedite my workload-- alot!!!! I've come to a place with it that it's not working as hoped. The problem is that when I select my source filter elements it selects other elements that I've not set, so on my destination page, it blanks out the groups that I am looking for. As I've made my modifications, I've tried paying close attention to the fields, and criteria to make sure that it's actually picking my desired elements... In examining the elements I've set for, for reasons I cannot identify, it sets one other element that I did not set up. I.e., I've made code for 3 elements from my source worksheet, and on the destination worksheet, it sets four. And I have made sure that it's selecting the right columns-- based on my criteria. My code is as follows-- it's a modification of the original source provided last year. -------------------------------- Dim wks As Worksheet Dim fname As String fname = range("a1") fname = Right(fname, 3) 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. mv2 = range("a1").End(xlUp).value 'this is meant to account for claim #. mv2 = fname For Each wks In ActiveWorkbook.Worksheets Select Case wks.Name Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " With wks Sheets(wks.Name).range("A14:G14").AutoFilter field:=1, Criteria1:=mv 'this takes in to acct the chg appl# for a filter. Sheets(wks.Name).range("A14:G14").AutoFilter field:=4, Criteria1:=mv1 'this takes in to account the owner name for a filter. Sheets(wks.Name).range("A14:G14").AutoFilter field:=2, Criteria1:=mv2 'this takes in to account the claim # End With End Select Next wks ---------------------------------------- sample data set Source page Column A- Owner's name Column F- 5 digit "change application #" "Claim#"- which I pull from the first row, and strip off the first 6-10 characters, only leaving the last 3 characters. destination page = "Sum*" Column D to match Column A on Source Column A to match Column F on source Column B to match "Claim#" from source. As I look back at the field for mv1, or my second autofilter, on the destination page, it's setting a text filter, instead of the filter that I "thought" I chose. Why is the text filter being set, and how can I prevent that? It was never a problem until recently. Or, perhaps I should say that if it had always set the text filter, the data that I sought, always matched the criteria. now there appears to be data sets that I'm using that don't match the text filter, and I don't understand why. Thank you for your helps. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
resolved filter issue
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...... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
autofilter with macro | Excel Discussion (Misc queries) | |||
Macro, AutoFilter... | Excel Discussion (Misc queries) | |||
Autofilter Macro? | Excel Discussion (Misc queries) | |||
Macro to Autofilter | Excel Programming | |||
autofilter using macro | Excel Programming |