Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date comparison | Excel Worksheet Functions | |||
Date comparison | Excel Discussion (Misc queries) | |||
Vlookup for data comparison/validation? | Excel Worksheet Functions | |||
VLOOKUP using another table for comparison values | Excel Worksheet Functions | |||
Date Comparison with VLookup | Excel Worksheet Functions |