+ Reply to Thread
Results 1 to 6 of 6

Advanced Filter Macros - Inconsistant Results

  1. #1
    Forum Contributor
    Join Date
    04-23-2014
    Location
    Liverpool...ish
    MS-Off Ver
    Excel 2013
    Posts
    126

    Advanced Filter Macros - Inconsistant Results

    Back again. I have spent the better part of a week trying to work through and understand this problem but, as you may guess, I am little the wiser.

    I am attempting to implement Advanced Filter routines using macros. Sometimes it works…. Sometimes it doesn’t. I can copy a workbook with a working filter and the filter in the new workbook will not work. I can create the same macro for two different places in the same workbook… one will work and one won’t. Always the Date/Advanced Filter function works manually. I have tried every bit of code that I can find and I can get consistent results. I don’t seem to find clear answers even though this appears to be a reoccurring question. Either I am the problem (likely) or Excel is (not unlikely).

    At any rate can anyone show me what I am doing wrong and how to get a consistent filter for the data presented here?

    Thanks
    Scott

    FilterProblem.xlsm

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,339

    Re: Advanced Filter Macros - Inconsistant Results

    1) advanced filter can copy the results only to the same sheet where data is located. It is working for you when you use structural addressing, because although data is in another sheet excel picks it up not directly from the sheet but with this structural address (so excel is in a sense fooled, where data really sits). Everything works fine if the results go to the same sheet as source:
    Please Login or Register  to view this content.
    2) using same names for procedures in different modules (normal and worksheet) is usually just "asking for trouble" - do it anly when you need.
    3) it is good to use sheet_qualified references to ranges. Sheetx.range("someaddress") or sheets("Sheet name").range("someaddress") basically alvays, and expecially as you are working with more than one sheet. Unqualified reference range("someaddress") is interpreted as activesheet.range("someaddress")

    Hope it gives some insight into your tests. see attachment with changes in your procedures (note that AZ1 and right/down in Lists is not cleared so clear manually to see the results)
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    04-23-2014
    Location
    Liverpool...ish
    MS-Off Ver
    Excel 2013
    Posts
    126

    Re: Advanced Filter Macros - Inconsistant Results

    Kaper,

    Thank you for responding. I was beginning to think that either my question / example or both was not understandable and no one was going to respond… at any rate, I thank you.

    1.My understanding with regards to copying was that ‘advanced filter’ only needed to be executed from the location(sheet) where the criteria was and that copying would reference that executing sheet…. My understanding anyway. Perhaps this is only a function of the ‘Advanced Filter’ control on the ribbon and not VBA code.

    2. I put the sheet together in a hurry but can see that creating two procedures with the same name can be a problem… point taken.

    3. This is where it gets sticky for me and the point of my confusion. I compared the Sub CashFilter_Click that resides both in the’ list’ sheet and in a module between my original file and your Zesty1 file. Yours and mine are identical except for the Debug.print statement, yet yours works and mind doesn’t. The answer to this question could clear a lot of fog for me because as I said, I have created workbooks where I have success and others where nothing works. Can you explain why your CashFilter_Click() operates differently than mine?

    Your assistance is much appreciated as I must be missing some basic concept of either the code or the filtering process.

    Kind Thanks
    Scott
    Last edited by TheScott; 04-02-2015 at 02:13 PM.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,339

    Re: Advanced Filter Macros - Inconsistant Results

    Spot the Easter Egg (we have such traditional folk game in Easter Monday :-) ):
    Please Login or Register  to view this content.
    As the procedure is in Sheet2 (Lists) the unqualified references are to Sheet2.

  5. #5
    Forum Contributor
    Join Date
    04-23-2014
    Location
    Liverpool...ish
    MS-Off Ver
    Excel 2013
    Posts
    126

    Re: Advanced Filter Macros - Inconsistant Results

    Well... Perhaps not exactly identical. I'm thinking the Easter bunny hid that egg AFTER I looked.... I mean, I am sure I looked there!

    Have a very pleasant Easter weekend and thank you again

    Scott

    After Thought: It was my understanding that a named range is global to the workbook, Why would a table name be considered unqualified?
    Last edited by TheScott; 04-03-2015 at 10:25 AM.

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,339

    Re: Advanced Filter Macros - Inconsistant Results

    If calling from general module - I'd assume qualification is indeed not needed and table name is global, but here you were calling not from the workbook level, but from the sheet level.

    Im not really sure, but as it works, then perhaps ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Advanced Filter Help - No Results in List Range
    By jcroucier in forum Excel General
    Replies: 12
    Last Post: 09-29-2014, 08:56 AM
  2. [SOLVED] Advanced Filter results don't match auto filter
    By WaterWings in forum Excel General
    Replies: 1
    Last Post: 09-13-2012, 05:37 PM
  3. Results of List of Data with Advanced Filter
    By vtphilk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2008, 06:21 PM
  4. Advanced Filter + Several Macros
    By excel_owns_me in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-12-2007, 02:52 AM
  5. [SOLVED] refresh advanced filter results
    By eagle in forum Excel General
    Replies: 2
    Last Post: 11-18-2005, 05:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1