+ Reply to Thread
Results 1 to 5 of 5

Automatic date selection in Excel 2007, VBA?

  1. #1
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Automatic date selection in Excel 2007, VBA?

    Hi,

    I would like Excel to automatically record which period each record belongs to instead of having users use a manual dropdown, but I'm not even sure if this is worth spending the time on.

    please see the attached.

    So Col B is where it should say reporting period for each row. I would like Excel to choose the right reporting period based on the date of arrival.

    In col K,L, and M, each period has a pair of start date and end date combination. So basically, the logic here is ......if the date of arrival (col c value) falls under....say for example 7/1/2011 and 7/31/2011, then I want Excel to give that row a P 1 under col B automatically, etc.

    Data collection will be on-going, so I don't want to have users drag the cell down as they enter new records, which can be cumbersome?

    Is this possible? am I making sense?

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Automatic date selection in Excel 2007, VBA?

    Paste the following into a new module in the VBA editor (Alt F11) (Insert -Module).

    Please Login or Register  to view this content.
    Then back in the main sheet type

    Please Login or Register  to view this content.
    into B2 and copy down.
    Martin

  3. #3
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: Automatic date selection in Excel 2007, VBA?

    Hi,

    Thank you.

    One question though...does this method ensure that I won't get any run-time error when the period changes? It is going to change starting...next April I believe. The start date and end date actually changes whenever a new fiscal year starts.

    When the time comes, I need to update start date and end date.

  4. #4
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: Automatic date selection in Excel 2007, VBA?

    Hi,

    I'm trying to put this start date and end date check directly in a worksheet_change event with other lines of code, but Excel seems to be bypassing it, and I'm not getting any errors.

    So the logic is...if a row's arrival date is in between a specific start date and end date, then I need to have Excel assign a quarter to that row.

    Please see the attached the code in the file. Could anybody help me on this please?

    Please Login or Register  to view this content.
    Thank you
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: Automatic date selection in Excel 2007, VBA?

    nvm, it is working now.

    How do I flag it as "solved" for others to see?

+ 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