Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2
This is the forum to discuss the Wrox book Excel 2007 VBA Programmer's Reference by John Green, Stephen Bullen, Rob Bovey, Michael Alexander; ISBN: 9780470046432
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 1st, 2009, 06:52 PM
Authorized User
 
Join Date: Sep 2007
Location: Carson City, NV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default AutoFilter

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.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Autofilter, multiselect, 2007 SteveB Excel VBA 0 March 5th, 2009 08:32 PM
Remove Autofilter yogeshyl Excel VBA 4 June 11th, 2007 06:59 AM
AutoFilter 5 columns at once Pindacko Excel VBA 0 April 26th, 2006 05:33 AM
Autofilter taking too much of time raaghu15 Excel VBA 2 March 4th, 2005 05:43 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.