Solved Rolling date in a macro

July 8, 2011 at 06:54:42
Specs: Windows XP
I work for a distribution company who source orders from all over the world, when a order becomes too old we have to chase it with the supplier.
We can export the buyin report to a CSV file and then save as a excel file. im trying to write a macro to flag the orders that are approaching the time limit. this i have manged to acheive but i cant get it to roll the date forward as the days roll forward (i.e to chase orders that are a week old and still not showing as dispatched) is this possible?

See More: Rolling date in a macro

Report •

#1
July 8, 2011 at 08:24:46
Perhaps it would help if you posted the macro you are currently using so that we can see if there is a simple modification that can be made.

If you are going to post code, please click on the following line and read the instructions on how to post code in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 8, 2011 at 08:48:04
Hi Thank you for your reply

The code i have so far is as below, i know its a little messy at the moment as its still a work in progress, any help you can give me would be greatly appreciated

ActiveWindow.SmallScroll Down:=-9
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=6, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "6/30/2011")
    Range("O2:O6").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Range("J685").Select
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=6
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=8, Criteria1:="="
    Range("O675:O677").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=8, Criteria1:="REJECT"
    Range("O577").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=8, Criteria1:= _
        "DISPATCHED"
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "6/30/2011")
    Range("O3").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "6/30/2011", 2, "7/3/2011")
    Range("O3:O76").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Range("Q15").Select
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=9
    Range("H1").Select
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=8, Criteria1:= _
        "COATING STRIP"
    Range("O36").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=8, Criteria1:= _
        "=COATING STRIP", Operator:=xlOr, Criteria2:="=ENTERED A&R"
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=8, Criteria1:= _
        "ENTERED TINTING"
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=9
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=8, Criteria1:="REJECT"
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=8
    ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=15, Criteria1:=RGB(255, _
        0, 0), Operator:=xlFilterFontColor
    ActiveWindow.SmallScroll Down:=-39
End Sub

Thank you again


Report •

#3
July 8, 2011 at 09:40:31
✔ Best Answer
Not knowing what your data looks like, the best I can do is to toss out this suggestion.

In this line I see hard-coded dates:

ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "6/30/2011", 2, "7/3/2011")

If you want to "roll the date forward as the days roll forward " you might be able to use the Date function instead.

I haven't tested this, but something like this should give you today's date and 3 days earlier as your filter criteria:

ActiveSheet.Range("$A$1:$Q$677").AutoFilter Field:=9, Operator:= _
xlFilterValues, Criteria2:=Array(1, Date - 3, 2, Date)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 8, 2011 at 10:13:51
Derbydad

You're genius, that is working a treat, and seems to be standing up to what im throwing at it, good man

Thankyou :-)


Report •

#5
Report •

Ask Question