Need help using date as an index

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott - 18 Sep 2007 15:36 GMT

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 to this Message

Vergel Adriano - 18 Sep 2007 16:18 GMT

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).

Signature

Hope that helps.

Vergel Adriano

> 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
[quoted text clipped - 16 lines]
> 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 to this Message