code filter data based on month & name doesn't work

[MKLAQ]

hi
I have this data in sheet source it should copy filter data based on two condition
if h1= name then filtering it's ok but if I select only i1 = month and if I select h1,i1 together the code doesn't work

date	name	inv no	case	paid	remained		alan	october
10/01/2021	alan	as123	paid	1000	2200
10/02/2021	john	as124	unpaid	-	1232
10/03/2021	alan	asd123	paid	1000	264
10/04/2021	john	asd124	unpaid	-	264
10/05/2021	roeberto	asd125	paid	1000	264
10/06/2021	sara	asd126	unpaid	-	264
10/07/2021	cali	asd127	paid	1000	264
10/08/2021	caroon	asd128	unpaid	-	264
10/09/2021	canin	asd129	paid	1000	264

'maniacb
Private Sub Worksheet_Change(ByVal Target As Range)
Dim month As String
Dim name As String
Dim lr, i As Long
Dim Rng As Range
Dim k As Integer
Dim source As Worksheet
Dim targetforecastmonth As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Range("H1:I1"), Target) Is Nothing Then

    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Set source = Worksheets("source")
    Set targetforecastmonth = Worksheets("result")
    month = source.Range("I1")
    name = source.Range("H1")
    For i = 1 To lr
        source.Range("G" & i) = source.Range("A" & i)
        source.Range("G" & i).NumberFormat = "mmm"
        source.Range("G" & i) = StrConv(source.Range("G" & i).Text, vbLowerCase)
    Next i

    targetforecastmonth.Cells(2, 1).CurrentRegion.Clear
    Set Rng = source.Range(Cells(1, 1), Cells(lr, 7))
    With Rng
        .AutoFilter
        If name <> "" Then
            .AutoFilter 2, Criteria1:=name
        End If
        If month <> "" Then
            .AutoFilter 7, Criteria1:=month
        End If
        .SpecialCells(xlCellTypeVisible).Copy
        targetforecastmonth.[A1].PasteSpecial xlPasteAll
        .AutoFilter
    End With

    source.Range("G1:G" & lr).Value = ""
    targetforecastmonth.Range("G1:G" & lr).Value = ""
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[mart37]

.AutoFilter 7 search in column 7, but column 7 is empty.

        If name <> "" And month <> "" Then
            .AutoFilter 2, Criteria1:=name
            .AutoFilter 7, Criteria1:=month
        Else
             If name <> "" Then
                 .AutoFilter 2, Criteria1:=name
             End If
             If month <> "" Then
                 .AutoFilter 7, Criteria1:=month
             End If
        End If

 

[MKLAQ]

you mean filter based on column 1 about month I did it nothing change

 

[mart37]

Because you wil match OCTOBER with 01/10/2021. That is not the same.
You need to rebuild a date from your text OCTOBER.
Something like this:

    arr = Array("january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december")
    monthnumber = Application.Match(month, arr, False)
    .AutoFilter Field:=1, Criteria1:= ">=" & monthnumber & "-1-2021", Operator:=xlAnd, Criteria2:="<" & monthnumber + 1 & "-1-2021"

 

[mart37]

deleted

 

[MKLAQ]

I tested your code but it gives me error about word month "argument not optional"
this is the code

Sub test()
Dim monthnumber As String
monthnumber = Range("i1").Value
     arr = Array("january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december")
    monthnumber = Application.Match(month, arr, False)
    .AutoFilter Field:=1, Criteria1:=">=" & monthnumber & "-1-2021", Operator:=xlAnd, Criteria2:="<" & monthnumber + 1 & "-1-2021"
End Sub

 

[mart37]

month= Range("i1").Value

 

[MKLAQ]

then shows another error in this word .AutoFilter Field:=1 "invalid or unqualified reference

 

[mart37]

Sub test()
    Dim monthnumber As String
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Set source = Worksheets("source")
    month = source.Range("I1")
    arr = Array("january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december")
    monthnumber = Application.Match(month, arr, False)
    Set Rng = source.Range(Cells(1, 1), Cells(lr, 7))
    With Rng
         .AutoFilter Field:=1, Criteria1:=">=" & monthnumber & "-1-2021", Operator:=xlAnd, Criteria2:="<" & monthnumber + 1 & "-1-2021"
     End With
End Sub

 

[mart37]

I don't know if the date format is correct.
When you simulate with hand the filter with the macrorecorder on. Then you can see the correct date format: - - or / /