Results 1 to 9 of 9

Autofilter from input box

This is a discussion on Autofilter from input box within the Excel Questions forums, part of the Question Forums category; Hi Im looking to filter a column based on a code the user puts into an input box. I know ...

  1. #1
    Board Regular
    Join Date
    Aug 2007
    Posts
    127

    Default Autofilter from input box

    Hi

    Im looking to filter a column based on a code the user puts into an input box.

    I know the first part of the code should be ok but the second i have just entered, if anyone can help!

    Sub CodeFilter()

    Dim oS, cl As Range
    oS = Application.InputBox("Please Enter Search Criteria")
    If oS = "" Then MsgBox "You must Enter a Code": Exit Sub
    If oS = "False" Then Exit Sub

    For Each cl In Range
    Rows("1:1").Select
    Selection.AutoFilter Field:=5, Criteria1:="oS", Operator:=xlAnd

    End Sub

  2. #2
    Board Regular
    Join Date
    Aug 2007
    Posts
    127

    Default Re: Autofilter from input box

    I now have it down to the following which selects row 1 but doesnt filter:

    Sub CodeFilter()

    Dim oS, cl As Range
    oS = Application.InputBox("Please Enter Search Criteria")
    If oS = "" Then MsgBox "You must Enter a Code": Exit Sub
    If oS = "False" Then Exit Sub

    Rows("1:1").Select
    Selection.AutoFilter Field:=5, Criteria1:=oS, Operator:=xlAnd

    End Sub

  3. #3
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Autofilter from input box

    If you look at the following:

    dim strInput as string
    strinput = Inputbox("Enter your value to filter on")

    then when your ready for the filter code add the strInput as the criteria.

    Sub mcrFilter()
    Dim strInput As String
    strInput = InputBox("Enter your value to filter on")
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$K$9359").AutoFilter Field:=1, Criteria1:= _
    strInput
    End Sub

  4. #4
    Board Regular
    Join Date
    Aug 2007
    Posts
    127

    Default Re: Autofilter from input box

    Quote Originally Posted by Trevor G View Post
    If you look at the following:

    dim strInput as string
    strinput = Inputbox("Enter your value to filter on")

    then when your ready for the filter code add the strInput as the criteria.

    Sub mcrFilter()
    Dim strInput As String
    strInput = InputBox("Enter your value to filter on")
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$K$9359").AutoFilter Field:=1, Criteria1:= _
    strInput
    End Sub

    Thanks for that Trevor - i need to use the contains strInput - everything I input doesnt work. Any ideas?

  5. #5
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Autofilter from input box

    Change the column number in my example to 5 which is indicated in yours then it should work

  6. #6
    Board Regular
    Join Date
    Aug 2007
    Posts
    127

    Default Re: Autofilter from input box

    Quote Originally Posted by Trevor G View Post
    Change the column number in my example to 5 which is indicated in yours then it should work
    I have changed to 5. When I go in to the filter, it is using equals rather than contains.

    Sorry to be a pest.

  7. #7
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Autofilter from input box

    Dan,

    You could use the * to search something like this:

    *Tea* which would find anything that has text before Tea and also anything after.

    Regards

    Trevor

  8. #8
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    6,158

    Default Re: Autofilter from input box

    Code:
    ActiveSheet.Range("$A$1:$K$9359").AutoFilter Field:=5, Criteria1:= "*" & strInput & "*", Operator:=xlAnd
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    24,780

    Default Re: Autofilter from input box

    You could also try:
    Code:
    Columns("E").AutoFilter Field:=1, Criteria1:="*" & strInput & "*"
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

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
  •  


DMCA.com