+ Reply to Thread
Results 1 to 21 of 21

Advanced filter error message

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Advanced filter error message

    Hello everybody
    I am trying to apply advanced filter but I got an error .. missing field ..
    Please Login or Register  to view this content.
    How can I filter data without using this method as this method depends on criteria table which is P1:S2 in sheet3?

    Thanks for help
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Advanced filter error message

    The code returns nil as there is no row which has four columns(All criteria) equal, hence the code does not error, but there is no data to copy.


    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    Thanks sir for help
    I got this error
    The extract range has a missing or invalid field name.

    * Not that I need the results to start in G5 not D2 as in your code

    * Another point is that I will not grab all the fields I will exclude "Sector" field from results

    * How can I filter data without using this method as this method depends on criteria table which is P1:S2 in sheet3?

    Thanks advanced
    Last edited by YasserKhalil; 06-28-2015 at 06:09 AM.

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    I could fix the problem by editing the formulas .. Instead of using "" I used "<>"&"" and the code was as follow:
    Please Login or Register  to view this content.
    Now it worked as expected

    My point now is there a way to get rid of the Criteria table or to store the table in an array and use it instead of the range("P1:S2")?

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Advanced filter error message

    I often find you spread sheet difficult to work with. It moves from left to right( I think this is due to Arabic writing), so I have created my workbook. See attached. There is no error on the code, but you get nothing to return except the header.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    Thanks Mr. AB33 for help
    I found your file is too large after extracting it .. but never mind
    I solved the advanced filter problem ..

    My point now is there a way to get rid of the Criteria table or to store the table in an array and use it instead of the range("P1:S2")?
    I don't want to depend on the sheet ? I hope it is clear now
    Thanks for help

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Advanced filter error message

    I am not entirely sure, but this is my believe.
    If the criteria value is a single line(row), yes you can put it in as array, like you use in a filter, but I do not think it works if you have over a single boundary, that is, the array will have a two dimension(Row and column). You can, however, use a range set, that is, you can put it in the criteria in a range.


    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    Thanks but that is not my target .. I don't want to use the range ("P1:S2") at all ..
    I imagine to put my conditions in an array and use it through filter method ..

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Advanced filter error message

    I will stick with post #7. You can try it yourself and see if you can use an array as a filter with advanced filter.

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    Mr. AB33 I tested post 7 and apply it to my file and it worked very well
    The case now is how to abandon using the criteria range itself ("P1:S2") and store the conditions on an array !!
    I don't know if it is clear or not >>>
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Advanced filter error message

    Your request is often clear, it is just the layout of your spread sheet which I find it difficult to navigate from right to left. I have also noticed this in posts from Israel. In post 7, I said I do not know if it is possible to use an array with advanced filter. I have run a test and getting an error.

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    Far from the attachement is the idea of using array to store the conditions possible or not ?
    If it was possible I can create a sample workbook with some data to apply the idea on it

  13. #13
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Advanced filter error message

    Yes, you can store values in to an array. Multi filter is an example. I have run my test and getting an error, hence for thinking that array will not work with advanced filter as I tried to explain in post 7.

  14. #14
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    817

    Re: Advanced filter error message

    Hello Yasser,

    It is an intermediate step (say a half).
    Maybe you can do something with it.
    Attached Files Attached Files
    Kind regards, Harry.

  15. #15
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    Hello HSV
    So we can create temp sheet and run the code and finally to delete the temp sheet ..That's a great step towards the target
    Thanks for this great contribution

    Can I use this line
    Please Login or Register  to view this content.
    to have the values in ranges. In other words :

    something like that :
    Please Login or Register  to view this content.
    And if any of the ranges mentioned in that line was empty so I mean to include all data in that field
    "<>" & "" as I used in formulas
    Attached Files Attached Files
    Last edited by YasserKhalil; 06-28-2015 at 11:57 AM.

  16. #16
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    Waiting for the second half of the great and successful idea

  17. #17
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    Is there a hope or I have to accept the half only?
    Please help me achieving that new idea for me

  18. #18
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Advanced filter error message

    Hi,

    Please Login or Register  to view this content.

    Regards

  19. #19
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    Mr. Karedog
    Really words are not enough to thank you for these great efforts
    Your code is exactly what I was searching for and I imagined in my mind the same steps but I couldn't translate it to vba lines ..
    Thank you very much for this helpful and wonderful code

  20. #20
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Advanced filter error message

    You are welcome, thanks for the reps.
    Maybe I should change my job to a translator someday


    Regards

  21. #21
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Advanced filter error message

    If you do and change your job to a translator, I'm sure you will be the most famous translator in the world

+ 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. Advanced Filter Error
    By csh8428 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2012, 05:56 PM
  2. [SOLVED] Advanced Filter Error
    By Floyd Bates in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2006, 02:20 PM
  3. Advanced Filter - error message
    By Mifty in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-25-2006, 02:10 PM
  4. Advanced Filter Error
    By Claret in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2006, 11:55 AM
  5. advanced filter error 1004
    By Tove in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2005, 04:06 PM

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