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? |
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? |
All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com