Product Filtering Between Two Dates Using Userform

Excel VBA Filter Between Two Dates

        Between the selected dates ,product-based filtering can be done using the userform.

        When the dates (start date,end date) are entered in text boxes and if report button is pressed  the userform  elongation effect is activated and listbox appears.
When choosing a date to add into text boxes,the date userform was used instead of date picker control :

excel vba filter two dates with userform

The found results are listed on the listbox.

Codes of "REPORT" button :
Private Sub CommandButton1_Click()
 Dim tarih1, tarih2 As Date: Dim ara As Range, LastRow As Long
    Dim s1 As Worksheet
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        Set s1 = Worksheets("P-1")
    If TextBox1.Value = "" Or TextBox2.Value = "" Then
    MsgBox "You need to add the beginning and end dates", vbCritical, ""
    Exit Sub
    End If
    If ComboBox1.Value = "" Then
    MsgBox "Please choose a product from drop-down list", vbDefaultButton1, ""
    Exit Sub
    End If
    Call uzat
    tarih1 = VBA.Format(TextBox1.Value, "dd.mm.yyyy")
    tarih2 = VBA.Format(TextBox2.Value, "dd.mm.yyyy")
 
    ListBox1.Clear
    ListBox1.ColumnCount = 9
    ListBox1.ColumnWidths = "30;70;140;30;80;65;80;65;60"
   
    LastRow = s1.Range("B" & Rows.Count).End(xlUp).Row
    For Each ara In s1.Range("B2:B" & LastRow)
    If CLng(CDate(ara.Value)) >= CLng(CDate(tarih1)) And _
    CLng(CDate(ara.Value)) <= CLng(CDate(tarih2)) And _
    CStr(ara.Offset(0, 1).Value) = CStr(ComboBox1.Text) Then
ListBox1.AddItem
            ListBox1.List(ListBox1.ListCount - 1, 1) = VBA.Format(ara, "dd.mm.yyyy")
            ListBox1.List(ListBox1.ListCount - 1, 0) = ara.Offset(0, -1)
            ListBox1.List(ListBox1.ListCount - 1, 2) = ara.Offset(0, 1)
            ListBox1.List(ListBox1.ListCount - 1, 3) = ara.Offset(0, 2)
            ListBox1.List(ListBox1.ListCount - 1, 4) = ara.Offset(0, 3)
            ListBox1.List(ListBox1.ListCount - 1, 5) = VBA.Format(ara.Offset(0, 4), "#,##.00")
            ListBox1.List(ListBox1.ListCount - 1, 6) = ara.Offset(0, 5)
            ListBox1.List(ListBox1.ListCount - 1, 7) = VBA.Format(ara.Offset(0, 6), "#,##.00")
            ListBox1.List(ListBox1.ListCount - 1, 8) = ara.Offset(0, 7)
                    End If
 Next ara
  Application.Calculation = xlCalculationAutomatic
 Application.ScreenUpdating = True
 Application.EnableEvents = True
End Sub

excel filter between two dates

3 comments:

  1. Very nice thank you. How can i change code if my product is in column D and not in C?

    ReplyDelete
  2. Very nice thank you. How can i change code if my product is in column E and not in C?

    ReplyDelete