Thanks:  0
Likes:  0

# Thread: Advance Fileter

1. 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 ]

2. 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.

3. 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?

4. 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.

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

6. 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"""

7. 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.

8. 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.

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

10. Try the following code:

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, NateO

[ This Message was edited by: nateo on 2002-05-08 19:45 ]

## User Tag List

#### Posting Permissions

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