+ Reply to Thread
Results 1 to 6 of 6

Using multiple Advanced Filters/different ranges?

  1. #1
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    198

    Using multiple Advanced Filters/different ranges?

    Hello all
    I would think it's possible to have more than one advanced filter in a workbook, but I keep getting errors. I'm using a multi-page form to manage two separate lists of data, each page would have the ability to search for existing data in its relevant list.

    So am I trying something not possible? Or just missing an obvious error?

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,370

    Re: Using multiple Advanced Filters/different ranges?

    Hi Rob,

    Sure, you can have multiple Advanced Filters in the same workbook. I used Named Ranges for my data and criteria (Dynamic Named Ranges really) and plug in the name in the Advanced Filter dialog. If you can show us a sample of what you have and/or tell us what error you are finding, we can help more.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    198

    Re: Using multiple Advanced Filters/different ranges?

    Thanks MarvinP, I will probably be embarrassed by the simplicity of this. I tried some simple copy and paste options without results. When
    the routine gets to the FilterCopy statement in the cmdInsideSearch click event of the Search form, I get a run-time error 1004 extract range has a
    missing or illegal field name error.

    The routine on the Exterior Projects page works great, and this is what I originally tried to copy and paste while changing the references as needed.

    Nothing sensitive in the workbook, it's really just a workbook to keep practicing VBA.

    Hope this helps you help me!

    Thank You
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,370

    Re: Using multiple Advanced Filters/different ranges?

    Hi Rob,

    You are working on some advanced stuff with a userform that is modeless and trying to drive an Advanced Filter with it. I'm not sure of the exact order of steps you are doing but I hang when the column head doesn't get to Y4 on the Projects sheet. Advanced Filters needs to have the column head word spelled correctly above the filter value(s) in the criteria range. It doesn't look like your macro builds the criteria range correctly.

    Another possible problem is you are programming using "CurrentRegion" which I think only is found on the active worksheet. You need to select that worksheet and then a cell to make the CurrentRegion work correctly. I also worry about your merged cells above where you want the current region to be. If the merged cells are taken to be the column heads for your filter, it won't work either. Another possible problem is having two column heads spelled exactly the same (or no text at all). I see you have Requirement for one table and Requirements (plural) for the other. Little differences like this can throw an error. IE, you filter for "Requirement" but the column head is "Requirements". Error as spelling is off.

    I hope the above helps you find the Advanced Filter problem. I'd need to study a lot more and step through your code to be more specific, but I don't know what steps you are doing and what order.

  5. #5
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    198

    Re: Using multiple Advanced Filters/different ranges?

    Hey Marvin,
    Thanks for the input. I am meticulously going back through everything and checking. I did finally notice the requirement/requirements error and fixed that. This method was copied from a utube video series and I've returned to watching it again to note every step is accurate.

    Using the CurrentRegion method is new to me so I don't fully understand the repercussions of using it.

    Thank you for the help. Now that I know it is possible to have multiple advanced filters I can keep plugging away knowing there is a correct process.

  6. #6
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    198

    Re: Using multiple Advanced Filters/different ranges?

    Just wanted to update this post for others that may be working with a similar situation. Both searches (Outside/Inside) are working now. Double clicking a listbox item returns the values to the text boxes for editing.

    I've not finished the edit process yet, but in other workbooks this has been relatively easy to complete. A few quirks remain, the Est Cost text box isn't displaying as $xx.xxx as shown on the worksheet.

    It's not a mile stone but maybe a 1/4 mile stone !!
    Attached Files Attached Files

+ 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. Replies: 3
    Last Post: 07-25-2014, 03:43 AM
  2. Advanced PivotTables??? - Multiple Data Filters in Rows...
    By wazzap_nz in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-15-2013, 09:32 PM
  3. Advanced filters with multiple criteria
    By Alicita in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-01-2011, 09:24 AM
  4. Multiple Filters ,Advanced Filter and Autofilter
    By capetownandre in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-28-2009, 03:23 AM
  5. Advanced Filters
    By Louise in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-28-2005, 07:05 AM
  6. advanced filters
    By jiwolf in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2005, 02:05 PM
  7. Advanced Filters
    By carlito_1985 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-29-2005, 02:40 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