Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help using date as an index
I have 2 workbooks. The first workbook has names in column A and dates in
column B. The names can be repeated for different dates. The workbook is sorted by column A then B. I have used the following to get the first date for a given name DBRowNumFirst = Application.Match(DBName, Range("A3:A2000"), 0) DBDate = Range("B" + CStr(DBRowNumFirst)).Value At the top of the workbook I have Dim DBDate As Date The second workbook has sorted dates in column A. I want to use DBDate to find the matching or first date greater than DBDate in this second workbook. I have tried this but I get an error 2042. SWDate1 = Application.Match(DBDate, Range("A4:A2000"), 3) I can use a loop to find what I am looking for but would like a simple function call like match or find. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help using date as an index
Scott,
Try replacing this line: DBDate = Range("B" + CStr(DBRowNumFirst)).Value with this: DBDate = Range("B" + CStr(DBRowNumFirst + 2)).Value That is because you started the search on A3 and Match will return the position of the value found..meaning if it returned a value of 1, then you need to get the value in column B, row 3. Next, try replacing this line: SWDate1 = Application.Match(DBDate, Range("A4:A2000"), 3) with this: SWDate1 = Application.Match(CLng(DBDate), Range("A4:A2000"), 3) date values in Excel are actually numbers (days passed since 1/0/1900). -- Hope that helps. Vergel Adriano "Scott" wrote: I have 2 workbooks. The first workbook has names in column A and dates in column B. The names can be repeated for different dates. The workbook is sorted by column A then B. I have used the following to get the first date for a given name DBRowNumFirst = Application.Match(DBName, Range("A3:A2000"), 0) DBDate = Range("B" + CStr(DBRowNumFirst)).Value At the top of the workbook I have Dim DBDate As Date The second workbook has sorted dates in column A. I want to use DBDate to find the matching or first date greater than DBDate in this second workbook. I have tried this but I get an error 2042. SWDate1 = Application.Match(DBDate, Range("A4:A2000"), 3) I can use a loop to find what I am looking for but would like a simple function call like match or find. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP or MATCH and/or INDEX to extract value on a given date | Excel Worksheet Functions | |||
Four criteria MATCH INDEX lookup of date between matching two text | Excel Worksheet Functions | |||
VLOOKUP MATCH INDEX two conditions / criterias text and date | Excel Worksheet Functions | |||
Question about using MIN/MAX, INDEX with Date range | Excel Worksheet Functions | |||
How to sum up a Year-to-date figure by using INDEX | Excel Discussion (Misc queries) |