Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Advance Fileter

  1. #11
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    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. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #14
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    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. #16
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #17
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #18
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #19
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #20
    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

    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 ]

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
  •