0

i have problem, that this macro is searching date with format mm/dd/yyyy and i need this format dd/mm/yyyy. Please help me to fix it

There is my code

 n = Format(Date, "dd/mm/yyyy")
 If Weekday(Now()) = vbMonday Then
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=12, _
    Criteria1:=">=" & (n- 4), Operator:=xlAnd, Criteria2:="<=" & (n)
Else
If Weekday(Now()) = vbTuesday Then
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=12, _
    Criteria1:=">=" & (n- 4), Operator:=xlAnd, Criteria2:="<=" & (n)        
Else
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=12, _
    Criteria1:=">=" & (n- 2), Operator:=xlAnd, Criteria2:="<=" & (n)
Salim
  • 11
  • 1
  • 5
  • VBA does not care about the date format. You must be sure that `Criteria1` is a long resulted from a `Date`. Please, try using `ActiveSheet.ListObjects(1).Range.AutoFilter Field:=12, _ Criteria1:=">=" & clng(Date) - 4, Operator:=xlAnd, Criteria2:="<=" & clng(Date)`. And use the second parameter to be sure that the correct week day is returned: `Weekday(Now(), vbMonday)` or whatever first day you like to consider, if it is not the English default... – FaneDuru Mar 12 '21 at 14:14
  • thanks its working – Salim Mar 12 '21 at 14:32
  • Glad I could help! – FaneDuru Mar 12 '21 at 14:49

0 Answers0