Date format VBA (versus Date format in Excel sheet)-CodePudding

I want to use a date (which is specified in a cell F2 = 01/11/2022) in a filter:

ActiveSheet.ListObjects("QuotationOverview").Range.AutoFilter Field:=5, Operator:= xlFilterValues, Criteria2:=Array(1, "11-2022")

As you can see I now have the date (month), hard coded "11-2022" but if I change that into Range("F2") the sorting is applied on January 2022. The VBA code expects the format mm/dd/YYYY but the format in Excel is dd/mm/YYYY.

ActiveSheet.ListObjects("QuotationOverview").Range.AutoFilter Field:=5, Operator:= xlFilterValues, Criteria2:=Array(1, Range("F2"))

How can I inform VBA that the format is dd/mm/YYYY and not mm/dd/YYYY?

CodePudding user response：

Try using Format to generate your month-year text values:

ActiveSheet.ListObjects("QuotationOverview").Range.AutoFilter Field:=5, Operator:= xlFilterValues, Criteria2:=Array(1, Format(Range("F2"),"mm-yyyy"))

CodePudding user response：

I just checked with an "mm/dd/yyyy" vs. "dd.mm.yyyy" case. If you use CDate(value) excel will convert whatever is written into your local dateformat IF the value is recognized as a date lateral.