Results 1 to 3 of 3

Thread: Solved: Set a variable as todays date minus X days.

  1. #1
    VBAX Newbie
    Nov 2010

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

    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[/VBA]
    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.


  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Apr 2005

    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
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie
    Nov 2010
    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.
    jk... If you ever need anything just pm me and I'd be happy to help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts