Trying to Filter months in a year

[Darren Smith]

I`ve written a code see below but it only filters on the Criteria1, not Criteria1 & 2
Can`t work out why any ideas? thanks in advance

        Private Sub Filter_Dates_Click()


        Dim StDate As Object
        Dim EndDate   As Object
        Dim ws As Worksheet
        Dim Fws As Worksheet
        Dim LRow As Long
        Dim Tbl As ListObject
        Dim Rng As Range
        Dim Ary As Variant

        Set ws = ThisWorkbook.Worksheets("TGS JOB RECORD")
        LRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
        Set Rng = ws.Range("J2:J" & LRow)
        
        Application.ScreenUpdating = False
        

         Set StDate = Me.cmbStart_Month
         Set EndDate = Me.cmbEnd_Month

        On Error Resume Next
        ws.ShowAllData
        
        If StDate >= EndDate Then
        MsgBox "Your Start Date is wrong"
        Exit Sub
        End If
        
        If IsEmpty(StDate) Or IsEmpty(EndDate) Then
        MsgBox "You need to sdd the beginning and end dates"
        Exit Sub
        End If
        
        With Rng
        .Autofilter Field:=10, Criteria1:=">=" & StDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
 
        End With
'        ws.AutoFilterMode = False
        
        Application.ScreenUpdating = True
        
        End Sub

 

[jasonb75]

What does Me.cmbStart_Month refer to? It is likely that it could be a text string instead of a valid date.

The other thing that comes to mind as you mentioned filtering by month is that all of the dates are in text format which could be problematic depending on the format used. (text dates only work properly with mm/dd/yy format, anything else goes wrong).

 

[Darren Smith]

Me.cmbStart_Month This is a Combobox Value with the month and year
I just need to filter between months so I don`t need days in the code.
I need MMMM/YYYY to work in the code is that possible

 

[jasonb75]

Is mmmm/yyyy how you enter the date into the combobox? If not, exactly which format are you using to enter the dates into the combobox?

How are the dates entered into the sheet? Are they proper dates with dd/mm/yy format, or something else? (if something else then what?)
To test if they are proper dates, select any single date cell, then try to change the format to general, if it changes to a 5 digit number then it is a proper date. If it doesn't change then it is a text string.

Matching dates that are in a text format from your combobox to any date in the sheet that is not in the exact same format can and will be problematic. I will need to you read what I have asked carefully and provide accurate responses, without that information I will not be able to help you with the problem.

 

[jasonb75]

I've just had something come up and might not be back on the forum for a few days.

Best thing I can suggest without knowing the details that I've asked for is to enter the dates as proper dates into the comboboxes, then use CDate(Me.cmbStart_Month) to convert the comboboxes to proper dates. Note that for the end date you will need to enter the last day of the month. It is possible that mmmm/yyyy might convert directly but where no day is specified it will always default to the first of the month.

 

[Darren Smith]

Please can somebody help with this?

 

[jasonb75]

Please can somebody help with this?

Can you please respond to posts 4 and 5? If you don't then people are either going to think that you have resolved it yourself or that you are ignoring the replies that you have received and will not be willing to waste their time on it.

 

[SQUIDD]

Jason

I think your on the right lines with the format, (looks like a combobox from user form maybe). I had a similar problem where my code was trying to filter but by string rather then date.

I had to use

Set StDate = CDbl(Me.cmbStart_Month)

as an example

 

[jasonb75]

In addition, I seem to remember that vba defaults to mm/dd/yy format when reading from text string dates. There are so many nuances to consider with this that I wouldn't even attempt to resolve it until the OP provides all of the information that I asked for.

@SQUIDD, I'm only going to be checking the forum once every 2 or 3 days, are you able to assist with this if needed? Based on your reply it looks as if you understand what is happening

 

[SQUIDD]

Hi Jason.

I can certainly have a go.
Although it will be tomorrow morning now.

Dave