Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Macro to filter column based on input box

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to filter column based on input box

    Hello,

    I am pretty new to VBA and I am currently trying to make a macro that applies a filter to a certain column based on the text a user enters into an input box. My worksheet has columns with months as headers from cell B2:Z2 in the format mmm'yy (i.e. Nov'15) and data below each month. I want the macro to allow the user to enter a month and year in the same format into an input box then for excel to find and select the column containing that month and year and apply a filter to the data.

    This is the code that I currently have but it is giving me an error:

    Sub Test()

    Dim iPutFound As Range
    Dim iPut As String

    iPut = Application.InputBox("Select Month", Type:=2)

    Set iPutFound = Sheets("Sheet1").Range("B1:Z1").Find(What:=iPut, _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, _
    MatchCase:=False)

    If Not iPutFound Is Nothing Then
    ActiveSheet.Range("$A$1:$Z$9359").AutoFilter Field:=iPutFound, Criteria1:="1"

    Else
    MsgBox "No match found."
    End If

    End Sub

    Thank you in advance!

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,898
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Macro to filter column based on input box

    The Autofilter Field number is the found column number in your case.

    ActiveSheet.Range("$A$1:$Z$9359").AutoFilter Field:=iPutFound.Column, Criteria1:="1"
    Last edited by AlphaFrog; Nov 9th, 2015 at 11:59 AM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Nov 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to filter column based on input box

    Thanks AlphaFrog! It works great!

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,898
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Macro to filter column based on input box

    You're welcome. Thanks for the feedback.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

Some videos you may like

User Tag List

Tags for this Thread

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
  •