View Full Version : Solved: Set a variable as todays date minus X days.

11-04-2010, 10:28 AM
My apologies beforehand if my problem is remedial or if any of my terminology is off. I've been using excel for close to ten years, but have just begun to explore VBA and I have a limited understanding of programming languages in general so the whole theory of programming is a bit hazy.
This forum has provided a great deal of help to me so far in getting established with vba so thank you to everyone who contributes here, but I now face a problem that I can not find a solution to by scouring the web.

I have a spreadsheet that contains all overdue invoices. Each record shows the invoice number, the customer it belongs to, due date, amount, etc.

What I'd like to have VBA do is read every due date and copy to separate sheets the invoices that are due today, 30 days overdue, and 60 days overdue.

My idea was to set a variable equal todays date for due today, todays date minus 30 days for thirty days overdue, and todays date minus 60 days for 60 days overdue. Something like:

Dim DueToday
Set DueToday = Date
Dim DueThirty
Set DueThirty = Date - 30
Dim DueSixty
Set DueSixty = Date - 60
Then scan the "Due Date" column for matches abd copy and paste the entire matched row to a certain sheet depending on how overdue the invoice is.

I have tried usinig a few examples from other posts but no ones examples matched and I am unable to fit the solution to my problem. Any input on how to set the variables correctly or scan the Due Date column would be awesome.


11-04-2010, 10:59 AM
WIth Activesheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "A").Value < Date - 60 Then

Row60 = Row60+1
.Cells(i, "A").Copy Worksheets("Sheet2").Cells(Row60,"A")

ElseIf .Cells(i, "A").Value < Date - 30 Then

Row30 = Row30+1
.Cells(i, "A").Copy Worksheets("Sheet2").Cells(Row30,"A")

ElseIf .Cells(i, "A").Value < Date Then

Row0 = Row0+1
.Cells(i, "A").Copy Worksheets("Sheet2").Cells(Row0,"A")
End If
Next i
End With

11-04-2010, 12:09 PM
Thanks XLD, that worked incredibly well and is much fast than the rest of my macro.
I don't care what everyone says about chileans, you guys are alright. :wink:
jk... If you ever need anything just pm me and I'd be happy to help.