Referencing code to a specific cell in Sheet1

[Nick70]

Hi,

I would like to amend this code so that the criteria2 references to a date in cell A2 Sheet1 (which would contain date 12/1/2023).

Option Compare Text
Sub test()

With Range("A3:d" & Cells(Rows.Count, "A").End(xlUp).Row)
k = .Find("DATE", lookat:=xlWhole).Column
        .AutoFilter k, Criteria1:=">=7/1/2023", Operator:=xlAnd, Criteria2:="<12/1/2023"
    End With
End Sub

How would I do that?

Thanks,
N.

 

[Joe4]

Perhaps:

Option Compare Text
Sub test()

Dim cr as String
cr = Format(Sheets("Sheet1").Range("A2").Value,"m/d/yyyy")

With Range("A3:d" & Cells(Rows.Count, "A").End(xlUp).Row)
    k = .Find("DATE", lookat:=xlWhole).Column
        .AutoFilter k, Criteria1:=">=7/1/2023", Operator:=xlAnd, Criteria2:="<" & cr
End With

End Sub

 

[Nick70]

Great thanks!
N.

 

[Joe4]

You are welcome.
Glad I could help!