xlfilteralldatesinperiod"variable" not working

[noidea23]

Sub testing()
a = InputBox("Month: ")

Sheets("Sheet1").ListObjects("SummaryTable").Range.AutoFilter Field:=4, Criteria1:=xlFilterAllDatesInPeriod"a", _
Operator:=xlFilterDynamic


Hello, I need help on how to filter data based on user input month. The month in the database is in the format e.g. 31/01/2017

 

[dmt32]

Hi
welcome to forum

See if this update to your code does what you want

Sub testing()
    Dim xlMonth     As Variant
    Do
        xlMonth = InputBox("Enter Month name: ", "Enter Month Name")
        'cancel pressed
        If StrPtr(xlMonth) = 0 Then Exit Sub
        On Error Resume Next
        xlMonth = Month(DateValue(xlMonth & "/" & Year(Date)))
    Loop Until Err = 0
   
    xlMonth = CLng(xlMonth) + 20
   
    Sheets("Sheet1").ListObjects("SummaryTable").Range.AutoFilter Field:=4, Criteria1:=xlMonth, _
                                                     Operator:=xlFilterDynamic
End Sub

Dave

 

[noidea23]

Hi
welcome to forum

See if this update to your code does what you want

Sub testing()
    Dim xlMonth     As Variant
    Do
        xlMonth = InputBox("Enter Month name: ", "Enter Month Name")
        'cancel pressed
        If StrPtr(xlMonth) = 0 Then Exit Sub
        On Error Resume Next
        xlMonth = Month(DateValue(xlMonth & "/" & Year(Date)))
    Loop Until Err = 0
  
    xlMonth = CLng(xlMonth) + 20
  
    Sheets("Sheet1").ListObjects("SummaryTable").Range.AutoFilter Field:=4, Criteria1:=xlMonth, _
                                                     Operator:=xlFilterDynamic
End Sub

Dave

it works! Thank you so much

 

[dmt32]

it works! Thank you so much

Most welcome - appreciate your feedback

Dave

 

[noidea23]

Hii, currently vba is reading my input dates (i.e., 28/2/2021) in the month-day-year format
I've tried modifying the line below to include Day(Date) but doesn't seem to be working:

xlMonth = Month(DateValue(xlMonth & "/" & Year(Date)))

Also, if im intending to filter by Year (from user input also) then Month, how should I go about doing it concurrently?

 

[dmt32]

Have a read of this article : How to Filter for Dates with VBA Macros in Excel - Excel Campus

& see if helps you

Dave