+ Reply to Thread
Results 1 to 9 of 9

VBA to sort various dates into values

  1. #1
    Registered User
    Join Date
    01-01-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    VBA to sort various dates into values

    Basically, i have a column full of dates. I need the macro to go through the column and then tally the number of dates for "1day over due" "7days over due" "14 days overdue" which is in a table over to the right of the column.. but I'm not sure how or where to start. Thanks in advance for your help

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA to sort various dates into values

    Suggest you post a copy of your worksheet so that a response can be tailored to your specific needs.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    01-01-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA to sort various dates into values

    Item Date Complete Target Date Over Due By # of Items
    1 9/01/12 1/01/2012 <7 days
    2 4/01/2012 1/01/2012 >7 days
    3 15/02/2012 1/01/2012 >14days
    4 20/01/2012 1/01/2012 >21 days
    5 etc
    6 etc
    7 etc

    Then actual table is a bit different but this gives you an idea. I need it to go down the second column and count up how many days are less than 7 days overdure, greater than 7 days overdue, etc.. and put it in a table to the right

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA to sort various dates into values

    This worked for me.
    Please Login or Register  to view this content.
    Alan

  5. #5
    Registered User
    Join Date
    01-01-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA to sort various dates into values

    Thank that works, only problem s I'm not used to using macro's, only VBA. Could you explain to me how the formula works or better if it is possible to do with using a formula. Thanks, sorry to be a pest

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA to sort various dates into values

    its a simple =If formula. If you were to create this outside of VBA, replace RC[-2] with A2 and RC[-1] with B2. Then you would copy it down from cell c2 where it originates. After running it. Look at the formulas in column C that it created. I gave you a VBA solution instead of a formula solution as that is what you asked for.

    Alan

  7. #7
    Registered User
    Join Date
    01-01-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA to sort various dates into values

    Sorry, misunderstanding/ didn't proof read
    Thank that works, only problem s I'm not used to using formula, only VBA. Could you explain to me how the formula works or better if it is possible to do without using a formula (=IF(RC[-2]-RC[-1]<7,""< 7 days"",IF(AND(RC[-2]-RC[-1]>7,RC[-2]-RC[-1]<14),"">7 days"",IF(AND(RC[-2]-RC[-1]>14,RC[-2]-RC[-1]<21),"">14 days"","">21days"")))). Thanks, sorry to be a pest
    I wanted to know what "RC" meant. The reason i didn't want it as a formula is the table the data is represented in is on another page and is used to create graphs which then go into a report. I'm not familliar with the formula so i wouldn't know where to begin editing it to suit.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA to sort various dates into values

    I'm confused by your request. Could you restate and tell me what you are looking for again. What doesn't the solution do for you? What do you need to be presented that is different? RC is the VBA representation to designate which cells to manipulate in the formula. It is the Row/Column designation in relation to the cell selected that is holding the calculation.

  9. #9
    Registered User
    Join Date
    01-01-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA to sort various dates into values

    Ok, so i have information in a table simillar to this except target date changes depending on the item:

    Item Date Complete Target Date
    1 9/01/12 1/01/2012
    2 4/01/2012 1/01/2012
    3 15/02/2012 1/01/2012
    4 20/01/2012 1/01/2012

    I need this information added according to how many days it is over due by ( eg. <7 DAYS, >7 DAYS, etc) and totals put into a table on another worksheet.

    In regards to your response:

    Sub CountDays()
    Dim lastrow As Integer
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Range("c2:c" & lastrow).FormulaR1C1 = "=IF(RC[-2]-RC[-1]<7,""< 7 days"",IF(AND(RC[-2]-RC[-1]>7,RC[-2]-RC[-1]<14),"">7 days"",IF(AND(RC[-2]-RC[-1]>14,RC[-2]-RC[-1]<21),"">14 days"","">21days"")))"

    End Sub
    The problem is that I'm not familiar with using formula ( This bit: "=IF(RC[-2]-RC[-1]<7,""< 7 days"",IF(AND(RC[-2]-RC[-1]>7,RC[-2]-RC[-1]<14),"">7 days"",IF(AND(RC[-2]-RC[-1]>14,RC[-2]-RC[-1]<21),"">14 days"","">21days"" ) and i don't know how to edit it to suit my table. I wanted to know if you could change the code so that it contained only VBA and not the above formula because i understand how to edit VBA and make it work. Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1