Filtering Criteria

[elgringo56]

I have tried asking this a couple of times with no viable results, which means I am not wording it correctly. So, I will try again. I have an inventory list up to 10000 items long (the rows) with several several entrys for each item (the columns). each item has a unique designator to identify it. the designator is an alpha numeric string such as MM1 or BB7A. An operator will type in the identifier looking for a single item. Key word here is single. A Macro will take her typein and filter the list for the item and return it to her. My problem is in the critera for the filter, the item number. If An item number of, say BB6 is used as criteria, all items that contain BB6 in them, such as BB61 or BB67 will be returned. I need only the one unique item, BB6 to be returned. I know I can do this by using a criteria of '=BB6 or ="=BB6" but I cant figure out how to get the added charaters onto the BB6 string under macro control? I need to do this because then, if more than one item is returned, it must be a duplicate or if no items are returned, it must be a bad item number.

Can anyone help me, I have tried for many hours to figgure this out myself, but I guess I am just plain stupid. AAAARRRRRGGGGHHHH

 

[EBK]

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

 

[elgringo56]

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

 

[elgringo56]

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.

 

[Aladin Akyurek]

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?

 

[elgringo56]

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

 

[elgringo56]

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

 

[NateO]

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

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:

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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-09 10:15

 

[elgringo56]

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.

 

[NateO]

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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-09 15:13

 

[elgringo56]

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