Advance Fileter

[elgringo56]

OK, once again my ignorance shows. I wish to filter/copy a list for the value MM1 located in one of the columns. I do not want the filter to display or find MM10, MM11, MM12........MM19. I have tried clicking on unique records only, thats doesn't do it. Is there any way to make what I want happen?

 

[Mark W.]

On 2002-05-08 15:30, elgringo56 wrote:
Oh, I think I do understand it. The "=MM1" portion says that it is a unique entry and the first = says it must equal that entry exact. would this be correct thinking? Thanks greatly guys for the help

Not quite. You're entering a formula that produces a text string, "=MM1". So the first = says, "this is a formula".

If this notion "bothers" you could also type...

'=MM1

In an criteria range Excel treats "MM1" differently than "=MM1". The later means exactly "MM1" while the former is equivalent to "MM1*" and means begins with "MM1".
This message was edited by Mark W. on 2002-05-08 15:42

 

[elgringo56]

doesn't bother me, Mark. Trying to learn as I go here and understanding what something is helps me kinda remember it. I really do apreaciate your help. Thanks a lot.

 

[Aladin Akyurek]

On 2002-05-08 15:40, Mark W. wrote:

On 2002-05-08 15:30, elgringo56 wrote:
Oh, I think I do understand it. The "=MM1" portion says that it is a unique entry and the first = says it must equal that entry exact. would this be correct thinking? Thanks greatly guys for the help

Not quite. You're entering a formula that produces a text string, "=MM1". So the first = says, "this is a formula".

If this notion "bothers" you could also type...

'=MM1

In an criteria range Excel treats "MM1" differently than "=MM1". The later means exactly "MM1" while the former is equivalent to "MM1*" and means begins with "MM1".
This message was edited by Mark W. on 2002-05-08 15:42

Just to make sure: You are not implying that the criteria as I posted will get the rows with values like MM10, are you?

 

[Mark W.]

On 2002-05-08 15:46, Aladin Akyurek wrote:

On 2002-05-08 15:40, Mark W. wrote:

On 2002-05-08 15:30, elgringo56 wrote:
Oh, I think I do understand it. The "=MM1" portion says that it is a unique entry and the first = says it must equal that entry exact. would this be correct thinking? Thanks greatly guys for the help

Not quite. You're entering a formula that produces a text string, "=MM1". So the first = says, "this is a formula".

If this notion "bothers" you could also type...

'=MM1

In an criteria range Excel treats "MM1" differently than "=MM1". The later means exactly "MM1" while the former is equivalent to "MM1*" and means begins with "MM1".
This message was edited by Mark W. on 2002-05-08 15:42

Just to make sure: You are not implying that the criteria as I posted will get the rows with values like MM10, are you?

No sir! I recognized that elgringo56 was using a traditional criteria and was providing a solution for that configuration. No need to switch to computed criteria.

 

[elgringo56]

If you see this mark, How can I, under macro contro get the '= or the ="=XXX" into my criteria? This has me stumped, also

 

[Mark W.]

On 2002-05-08 15:53, elgringo56 wrote:
If you see this mark, How can I, under macro contro get the '= or the ="=XXX" into my criteria? This has me stumped, also

ActiveCell.FormulaR1C1 = "=""=MM1"""

 

[elgringo56]

Ok, but here is my problem. I have a list of inventory items, each item has a code such as MM1, the list can be 10000 items deep. Operator keys in that he is looking for MM1. under macro control, I have to append the '= to the MM1, which seems to work well. so far, I havn't figured out how to do that. Nothing I have tried seems to work.

 

[Mark W.]

On 2002-05-08 16:03, elgringo56 wrote:
Ok, but here is my problem. I have a list of inventory items, each item has a code such as MM1, the list can be 10000 items deep. Operator keys in that he is looking for MM1. under macro control, I have to append the '= to the MM1, which seems to work well. so far, I havn't figured out how to do that. Nothing I have tried seems to work.

Here's where I "run dry". I'm not a VBA wizard. I assume that once choosen "MM1" will be stored in a variable. Repost and explicitly ask how to replace "MM1" in the statement...

ActiveCell.FormulaR1C1 = "=""=MM1"""

...with the contents of a variable.

 

[elgringo56]

Ok, I will try and figgure out how to ask this. Thanks a lot, Mark

 

[NateO]

Try the following code:

Sub F_and_R()
Dim cl As Range, n As String
STerm = InputBox("Enter your Search Term")
For Each cl In [a1:a10000] 'enter your range here
If cl.Value<> "" And _
Application.WorksheetFunction.IsText(cl.Value) Then
    n = cl.Value
    If Left(n, 3) = STerm Then _
        cl.Value = "=" & n
End If
Next cl
End Sub

This takes the first 3 letters of each cell in your range (a1:a10000 above) and if they equal the inputbox text, an equal sign will be inserted as the first character. It will overwrite formulae if the first 3 characters in the solution equal the inputbox.


Change a1:a10000 to your target range. 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-08 19:45