Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Filtering Criteria

  1. #11
    New Member
    Join Date
    Apr 2002
    Location
    Paul, Sydney Australia
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Like above, its a bit confusing at first, if it is possible to send me a sample I will put something together to test and should be able to eliminate the need for a macro.


    Sincerely


    Paul

  2. #12
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It has to be under macro control. A input person inputs the value that will be looked for as an inventory item number. I only want one item to be returned because of the following. If two items are returned, one must be a duplicate or, if no items are returned, then the input must be an error. Aladin, I want to thank you for your help. I have to say this, though, I am a true novice at this and the answer you gave me at the first was so far over my head, I had no idea what it was saying. I have much to learn

  3. #13
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It is just a list of alpha numerics. I need to be able to filter out a single entry from that list. However, a simple filter using something like AA5 as criteria will not get me a single find. any cell that contains AA5 will be found, such as AA53. If filtering manualy, I could enter ="=AA5" or '=AA5 and find only the AA5 entry. But, under macro control, I dont know how to do this.

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-09 06:28, elgringo56 wrote:
    It has to be under macro control. A input person inputs the value that will be looked for as an inventory item number. I only want one item to be returned because of the following. If two items are returned, one must be a duplicate or, if no items are returned, then the input must be an error. Aladin, I want to thank you for your help. I have to say this, though, I am a true novice at this and the answer you gave me at the first was so far over my head, I had no idea what it was saying. I have much to learn
    You want me to send you a copy the WB showing how you can use a computed criterion in Advanced Filter?

  5. #15
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Aladin, that might help. I'm at elgringo56@prodigy.net.mx. Thats a Mexico address as I am semi retired down here.

  6. #16
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If anyone else has any ideas for a a simple solution for this, feel free to add it. I will try anything at this poing

  7. #17
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's a thought. To hide all rows not meeting your criteria try:

    Code:
    Sub Filt()
    Dim cl As Range
    Sterm = InputBox("Enter your Search Term")
    [1:10000].EntireRow.Hidden = True ' Hide All
    For Each cl In [a1:a10000] 'enter your range here
    If cl.Value = Sterm Then cl.EntireRow.Hidden = False 'Unhide rows meeting criteria
    Next cl
    End Sub
    The code above works much, much faster than hiding rows that don't meet your criteria. Change the a to the column your data is in and row numbers to those that are applicable. To unfilter the data, try:

    Code:
    Sub UnFilt()
    [1:65536].EntireRow.Hidden = False
    End Sub
    If you enter mm1, only mm1 will show (e.g., mm16 will be filtered out). Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-09 10:15 ]

  8. #18
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Nate, this may have to be the route I go. However, remember, I am a total novice. A couple of things I dont know here. Once the cell containing my criteria is unhidden and all others are hidden, how do I find that cell and its accompaning row of data and then pull it out? What happens if the input person made a mistake and input a value that isn't there and how will I know that? What if there is a duplicate entry and two cells are open, how would I know that? Lastly, would the third instruction look something like this if the criteria were in a known cell like cell AS10.

    Sterm = Range("AS10").Value

    Remember, I am ignorant on this stuff, but trying to learn.

  9. #19
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let's see.

    You will see only the row of data that has your criteria. So, if there is one cell with this you will see that row of data, then row 10,000 and on. If there are no matches, the first row you see will be 10,000. If there are duplicates you will see duplicate rows before row 10,000. E.g., row 10, 150 & 9999.

    I assume all of your criteria is in one column. If your criteria is in column AS, change the following:

    [a1:a10000] to [as1:as10000]

    If as10 is a match, you will see row 10, then row 10,000.

    Don't do this:
    Sterm = Range("AS10").Value, Sterm is your input box value (the value you entered in your input box.

    Run the other procedure to unhide everything.

    This procedure does not manipulate data in any way, so try it out and see how it goes. Just paste it in a normal module.

    Hope this helps. If there are more questions or concerns, post back.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-09 15:13 ]

  10. #20
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A slight misunderstanding here, I think, Nate. All of this is withing a macro. I must extract that data found from within the macro and move it into a form, So I wont see any of it at the point of filter. How do I find with a macro, what was extracted? All that I am doing is withing macros. The input person puts in a code, and then gets back in a form, the resultant inventory item. I do appreciate yours and all the other peoples help that have been trying to help me solve this. Seems funny to me that I cant Auto filter for a single value under macro control. If I could figgure out how to change the criteria value by adding the characters '= in front of them, auto filter would work fine, but I cant seem to do that under macro control

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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