VBA Vlookup using high & low date comparison
 

I have a spreadsheet that uses a code (CWW1, CWW2, CWW#, etc) to determine
whether a cell will have the value €œCWW€� based on a persons name and a
corresponding weekday date of the month. Using a monthly low date (Start Date
Apr 1, 2009) and a monthly high (End Date April 30, 2009, the code uses
Vlookup to search a named range (CWWLIST) and pick out a date that falls in
between the low & high dates in the CWW column (i.e. €œCWW6€�). This is
extremely complicated for me to figure out but I do need it for a spreadsheet
that my Manager has requested.

Source Code
----------------
Low Date - Cell BA1 €“ 04/01/2009
High Date - Cell BB1 €“ 04/30/2009
CWW Code - wsPh.Range("a" & emcnt) €“ €œCWW6€�
Employee Name - wsPh.Range("ai" & emcnt)


CWWList Named Range
------------------------------

(Columns) A B C D E F
CWW1 CWW2 CWW3 CWW4 CWW5 CWW6 (Row 1)
3/16/09 3/20/09 3/23/09 3/27/09 3/30/09 4/03/09

Target Worksheet
----------------------

(Columns) A B C D E
Weekdays M T W TH F (Row 3)
1 2 3 (Row 4)
€œ€� €œ€� CWW (Row 5)

The date in €œE4€� should be = to Low Date in €œBA1€� and <= High Date in
€œBB1€� to make sure it is within the monthly range and then the formula that
is placed in €œE5€� should do a vlookup in range CWWList to find the date
€œ04/03/2009€� which will result in €œCWW€� placed in cell €œE5€�. The formula
placed by VBA code should look something like this.

=IF(AND(DATE(YEAR(H$3),MONTH(H$3),DAY(H$3))<=DATE( YEAR($AM$1),MONTH($AM$1),DAY($AM$1)),DATE(YEAR(H$3 ),MONTH(H$3),DAY(H$3))<=DATE(YEAR($AN$1),MONTH($AN $1),DAY($AN$1))),€�Vlookup,CWWLIST,x,xx€�,€�€� )