Results 1 to 6 of 6

Thread: [RESOLVED] Excel VBA: Table, Field Search "Text Filters--> Contains"

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Resolved [RESOLVED] Excel VBA: Table, Field Search "Text Filters--> Contains"

    I need a macro that will search the first column of my table for a specific entry. I have seen code that I've tried to use, but because my database is arranged as a table I suspect that that is the reason it isn't working.

    Example: my repetitive action is that I have to click on the column sort button every time I need to find something and then I have to manually select "Text Filters" and then "Contains" and then type what I'm looking for.

    I need a button which will allow me to just type in what I'm looking for instead of having to do this action manually 50-60 times a day.

    Below is an example of what did not work, but is similar to what I'm looking for.
    Code:
    ActiveSheet.Range("$A$1:$A$152184").AutoFilter Field:=1, Criteria1:= _
            "=*" & Range("J3").Value & "*", Operator:=xlAnd

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel VBA: Table, Field Search "Text Filters--> Contains"

    is there an easy way to reference the table (Named: Contra) so as to cut down on compiling, and to ID a specific column for searching the inputted text?

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel VBA: Table, Field Search "Text Filters--> Contains"

    can you attach an example of the before and after?

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel VBA: Table, Field Search "Text Filters--> Contains"

    vbfbryce... I nailed it down myself (super proud of that lol)... took some extra thinking it through but I'm getting better. I'll post the solution and some screenshots of how it works when I get back to my computer.

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel VBA: Table, Field Search "Text Filters--> Contains"

    Sounds good, and congrats!

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel VBA: Table, Field Search "Text Filters--> Contains"

    So, as mentioned before I had to manually sift the same column by needing to: Sort Button --> Text Filters --> Contains ... then type in what I'm looking for... sounds easy, but it comes with a slew of problems when you have to research 20-40 contracts in a row.

    The solution was to create a macro which would take out the potential for error, while still providing the same search... I took it a step further and created input boxes and code which would not only do the search but also inform the user what input they're researching.

    Before:
    Name:  The Works.jpg
Views: 2811
Size:  31.0 KB

    During:
    Name:  The works2.jpg
Views: 2758
Size:  32.4 KB

    After:
    Name:  the works3.jpg
Views: 2771
Size:  34.6 KB

    Here's the code I used for BOTH the search function, as well as the clear sorts function:

    Code:
    Sub Find_Files()
    
    Dim strResult As String
    
        strResult = InputBox("Please type the first 3 to 5 letter of your search and press ok." _
        , "Let's Find Your File!")
        
    ActiveSheet.Range("E4").Select
    ActiveCell.Value = strResult
    
    
    ActiveSheet.Range("CDB").AutoFilter Field:=4, Criteria1:= _
            "=*" & Range("E4").Value & "*", Operator:=xlAnd
    End Sub
    
    Sub Clear_Sorts()
    '
    'Turns off the screen updating while eliminating the need to manually
    'erase the input reference.  Also clears the sorted fields and
    'resets the selection back to cell A1.
    
    Application.ScreenUpdating = False
    Range("E4").Select
        Selection.ClearContents
    '
        Range("Table1[[#Headers],[Reporting]]").Select
        ActiveSheet.ShowAllData
        
    Range("a1").Select
    
    
    End Sub
    I hope this helps anybody who has had similar questions, if so I'm glad I could finally contribute lol

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width