Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #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.
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.....
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 07:06 AM.

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