VBA Code to apply filter based on user inputboxes for column alpha-numeric and date

[kkbakic]

I would like to write vba code that asks the user "which column" they want to filter; and then "what date" they want to filter that column by (ie any date after mm/dd/yyyy)

Something like:

Sub FilterByInput()

Dim iPutFound As Range
​

Dim mycol As String​

Dim mydate as Variant
​

mycol = Application.InputBox("Enter alpha-numeric of Column to be filtered -ie. AP3)
​

mydate = Application.InputBox("Enter date since last update was ran- mm/dd/yyyy")​

Application.ScreenUpdating = False​

Range(mycol).Select​

ActiveSheet.Range ("$A$1:$DK$11000).Autofilter Field = Selection.Column, Criteria1:=Array(mydate), Operator=xlFilterValues​

'The code will resume with copying and pasting the filtered results to another sheet, etc
​

End sub​

 

[igold]

Does this get you any closer to what you want...

Sub FilterByInput()


    Dim iPutFound As Range
    Dim mycol As String
    Dim mydate As String
    
    mycol = Application.InputBox("Enter alpha-numeric of Column to be filtered -ie. AP3", Type:=2)
    mycol = Range(mycol & 1).Column
    mydate = Application.InputBox("Enter date since last update was ran- mm/dd/yyyy", Type:=2)
    Application.ScreenUpdating = False
    ActiveSheet.Range("$A$1:$DK$11000").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues
    
    'The code will resume with copying and pasting the filtered results to another sheet, etc


End Sub

 

[kkbakic]

Thanks igold. I think it gets me closer, but I get an "Autofilter method of Range class failed" run-time error when I get to the following line:
ActiveSheet.Range("$A$1:$DK$11000").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues

I tried changing it to:

 

[kkbakic]

Thanks igold. I think it gets me closer, but I get an "Autofilter method of Range class failed" run-time error when I get to the following line:
ActiveSheet.Range("$A$1:$DK$11000").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues

I tried changing it to:

ActiveSheet.Range("A3:DK3").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues

because my header row is on row 3, but that didn't work. Also tried turning the filters off before running the code, but that didn't work either.
​

 

[igold]

Running this code with the sheet that you want filtered as the activesheet, does this work. I filled a sheet with gibberish, except for Column AF which I put dates in, with Row 3 as the header row and this code tested fine. The only caveat I found was that your dates on the activesheet must be in the format of "mm/dd/yyyy".

Sub FilterByInput()


    Dim iPutFound As Range
    Dim mycol As String
    Dim mydate As String
    
    mycol = Application.InputBox("Enter alpha-numeric of Column to be filtered -ie. AP3", Type:=2)
    mycol = Range(mycol & 1).Column
    mydate = Application.InputBox("Enter date since last update was ran- mm/dd/yyyy", Type:=2)
    Application.ScreenUpdating = False
    ActiveSheet.Range("$A$3:$DK$11000").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues
    
    'The code will resume with copying and pasting the filtered results to another sheet, etc


End Sub

 

[kkbakic]

You're right igold. It works fine when I make a "test" worksheet. There must be some other conflict. I'll keep working at it, but it's good to know that it should work! Thanks so much for your help. You rock!

 

[igold]

You're welcome. I was happy to help. Thanks for the feedback!