Announcement

Collapse
No announcement yet.

Filter Data Between Two Dates

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Filter Data Between Two Dates



    hi, i found this code somewhere. this code was supposed to filter data for a period of 30 days.
    my case is that i have 2 strings of dates. in the dd-mmm-yy format. both of the dates can be any day. how do i modify this code to suit my case?

    Code:
    Filtermonth Format(Sheets("Reference").Cells(ComboBox1.ListIndex + 2, 19), "dd-mmm-yyyy"), _
    Format(Sheets("Reference").Cells(ComboBox6.ListIndex + 32, 19), "dd-mmm-yyyy")
    and the support code for the "FilterMonthSolvent" is as below:
    Code:
    Sub FilterMonthSolvent(strCriteria3 As String, strCriteria4 As String)
        With Sheet2
            .AutoFilterMode = False
            .Range("A1:O1").AutoFilter
            .Range("A1:O1").AutoFilter Field:=3, Criteria1:=">=" & strCriteria3, _
            Operator:=xlAnd, Criteria2:="<=" & strCriteria4
        End With
    End Sub

    thanks!

  • #2
    Re: Filter Data Between Two Dates

    The code will still do this for (ie the sub itself, your second code example).

    The first example is where the code is called from and this is the bit you'll need to amend. All you need to do is pass the dates in questions...so the call would be
    Code:
    Filtermonth StartDate, EndDate
    D

    Better a bad day on the water than a good day in the office

    Comment


    • #3
      Re: Filter Data Between Two Dates

      thanks for your prompt reply. how do i define the "startdate" and "end date"? actually my date is take from 3 combo boxes. which one is for year, one is for month, another one is for day. after that i concentrate them together into one string, as below:
      Code:
      Sub Concentrate()
      Dim ConcDate1 As String
      ConcDate1 = ComboBox5.Text & "-" & ComboBox4.Text & "-" & ComboBox3.Text
      End Sub
      how do i call them? in the function "FilterMonth"?

      thanks again.

      Comment


      • #4
        Re: Filter Data Between Two Dates

        Using that sub - you would have to make ConcDate1 a global variable so that it can be used outside of the routine itself (see VBE help on Understanding the Lifetime of Variables). It is also possible to dim this as a date instead of a string.

        I presume you have a ub for both the start and end date though...

        The code in my last post shows how you would call the function itself
        D

        Better a bad day on the water than a good day in the office

        Comment


        • #5
          Re: Filter Data Between Two Dates

          erm..sorry.. i still don't quite understand. first, i tried it in this way:
          Code:
          Private Sub OkButton_Click()
          If ComboBox8.Value = True Then
          FilterMonth StartDate, EndDate
          end if
          End Sub
          
          'the code to filter is as the sub i mentioned above
          
          Sub Concentrate()
          Dim ConcDate1 As Date
          ConcDate1 = ComboBox5.Text & "-" & ComboBox4.Text & "-" & ComboBox3.Text
          End Sub
          
          Sub Concentrate1()
          Dim ConcDate As Date
          ConcDate = ComboBox8.Text & "-" & ComboBox7.Text & "-" & ComboBox6.Text
          End Sub
          
          Sub StartDate()
          Dim StartDate As Date
          Call Concentrate
          StartDate = ConcDate1
          End Sub
          
          Sub EndDate()
          Dim EndDate As Date
          Call Concentrate1
          EndDate = ConcDate
          End Sub
          In the end, it still doesn't work.
          To my knowledge and to my case, for the function "FilterMonth", we have to assign the location(the exact cell) of startdate and enddate for the function to filter. isn't it?
          For my code below, the two variables for the function to filter are two columns of dates of a year (365 days) .
          if not, how to filter the date not according to their location but the date itself?

          Code:
          FilterWeek Format(Sheets("ExtractedData").Cells(ComboBox9.ListIndex + 2, 1)), _
          Format(Sheets("ExtractedData").Cells(ComboBox10.ListIndex + 2, 1))
          thanks for helping me.

          Comment


          • #6


            Re: Filter Data Between Two Dates

            I would try something along these lines...if this doesn't point you in the right direction - are you able to post an example of the workbook to have a look at ??
            Code:
            Sub FilterMonthSolvent(strCriteria3 As String, strCriteria4 As String)
                
                'This will apply an autofilter on columns A:0 on Sheet2
                'Then on the third column of the list
                'filter between the dates selected
                
                With Sheet2
                    .AutoFilterMode = False
                    .Range("A1:O1").AutoFilter
                    .Range("A1:O1").AutoFilter Field:=3, Criteria1:=">=" & strCriteria3, _
                    Operator:=xlAnd, Criteria2:="<=" & strCriteria4
                End With
            End Sub
            
            Sub testit()
            Dim dStartDate As Date, dEndDate As Date
            
            If combobox8.Value = True Then
                dStartDate = DateSerial(combobox5.Value, combobox4.Value, combobox3.Value)
                    'These would be in Year, Month, Day Sequence
                    'and the values are required to be numeric
                    'you could also take the listindex of the combobox
                dEndDate = DateSerial(combobox8.Value, combobox7.Value, combobox6.Value)
                    'See notes above
                FilterMonthSolvent dStartDate, dEndDate
            End If
                   
            End Sub
            D

            Better a bad day on the water than a good day in the office

            Comment

            Working...
            X