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

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.

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.

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

nvm, it is working now.

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