VBA macro to check date against list

[slick9456]

Hopefully someone can shed some light on what's the best way to approach this issue I am having.


Assumptions


-Column E contains dates

-Column G contains either "Exempt" or "Non-Exempt" in the cells downward

-Column C contains either "IFS", "Tax", "FAS" or "ASR"


The goal is to have the macro check the date in column E against 1 of 4 lists based on the following criteria. Due to limitations with the way the system exports the data, I need to have everything in the macro including the lists of dates. If the date is on the list its supposed to check, then "On-cycle" otherwise "Off-cycle". The criteria to choose a specific list would be:


1. If Column G contains "Non-Exempt" check list 1

2. If Column G contains "Exempt" and Column C contains "Tax" or "IFS" check list 2

3. If Column G contains "Exempt" and Column C contains "FAS" check list 3

4. If Column G contains "Exempt" and Column C contains "ASR" check list 4*


My original thought was to simply write out a very long nested IF formula and have the macro insert the formula, however I realize that this is not the most efficient way to do this, especially considering those lists of dates change on a yearly basis.


While I am not opposed to setting a yearly reminder to update these dates, I would like to try and streamline this. Looking for some help as I am fairly new to vba. I did some reading and think this might be an opportunity to use array's for the 4 lists of dates, but I have no clue how to set one up to check against the date based on the criteria.


Or otherwise, if it's possible to check that very same criteria against google sheet or even any other type of hosted spreadsheet.


Really appreciate any insight and help.



Thanks!

[BS Singh]

Please attach a sample file showing some dummy data with desired output..

[slick9456]

Apologies on the delayed response, I have been without access to my computer with the data till just now. Here is the sample data. The results of the macro running the check would ideally spit out "On-Cycle" or "Off-Cycle into the green column.

Also the 4 lists to check the dates from would again be ideally in the VBA or possibly linked to a google sheet and would include the following dates:

List 1 (Non-Exempt):

10/09/2014
10/23/2014
11/06/2014
11/13/2014
12/04/2014

List 2 (Exempt & FAS)

10/06/2014
10/20/2014
11/03/2014
11/10/2014
12/01/2014

List 3 (Exempt & ASR):

10/06/2014
10/20/2014
11/03/2014
12/01/2014

List 4 (Exempt & Tax or IFS)

10/06/2014
10/20/2014
11/03/2014
11/10/2014
12/01/2014

https://drive.google.com/file/d/0B2X...ew?usp=sharing