Autofilter with International Dates

[Danl8619]

Hi all,

I've created a macro that autofilters everything within the next 7 days of the current date along with several other criteria. It works perfectly on my computer in the US, but my UK counterpart seems to come up with incorrect rows. My guess is that it is a date formatting issue, but I'm not sure how to get around the problem. I have posted the macro below.

Private Sub Upcoming_Bids_Click()

Dim MyDate As String
Dim MyDatePlus As String

MyDate = Now()
MyDatePlus = DateAdd("d", 7, Now())

ActiveSheet.Unprotect

Selection.AutoFilter Field:=1, Criteria1:="=Propose to Bid", Operator:=xlOr, Criteria2:="=Propose to Re-Bid"
Selection.AutoFilter Field:=21, Criteria1:="=PLC", Operator:=xlOr, Criteria2:="=SBU"
Selection.AutoFilter Field:=14, Criteria1:=">=" & CStr(MyDate), Operator:=xlAnd, Criteria2:="<=" & CStr(MyDatePlus)

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
    False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
    :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
    AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
    AllowUsingPivotTables:=True


End Sub

 

[Domski]

Try it with your variables as long rather than string:

Private Sub Upcoming_Bids_Click()
Dim MyDate As Long
Dim MyDatePlus As Long
MyDate = Date
MyDatePlus = DateAdd("d", 7, Date)
ActiveSheet.Unprotect
Selection.AutoFilter Field:=1, Criteria1:="=Propose to Bid", Operator:=xlOr, Criteria2:="=Propose to Re-Bid"
Selection.AutoFilter Field:=21, Criteria1:="=PLC", Operator:=xlOr, Criteria2:="=SBU"
Selection.AutoFilter Field:=14, Criteria1:=">=" & MyDate, Operator:=xlAnd, Criteria2:="<=" & MyDatePlus
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
    False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
    :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
    AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
    AllowUsingPivotTables:=True

End Sub

Dom