Excel Hints

Results 1 to 3 of 3

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

  1. #1

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

    Hi,
    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:
    VB:
    Dim DueToday 
    Set DueToday = Date 
    Dim DueThirty 
    Set DueThirty = Date - 30 
    Dim DueSixty 
    Set DueSixty = Date - 60 
    
    
    Formatting tags added by mark007
    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.

    Cheers!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,022
    Location
    VB:
     
    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 
    
    
    Formatting tags added by mark007
    ____________________________________________
    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
    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
  •