1st you match formula doesn't error if date not found unless you change to
,0
try this
Sub finddate()
MsgBox Columns(5).Find(Date - 1).Row - 1
End Sub

--
Don Guillett
SalesAid Software

"Hari" wrote in message
ups.com...
Hi,

I have some dates in column E (starting from 31-Jan-2005 to 31-Oct-09
-- 3 Month stretch)

If I put the following formula in my excel worksheet

=MATCH(TODAY()-1,$E$2:$E$21,1)

then I get the correct value as 6.

But If I write in VBA

RowNoForReportRange = Application.WorksheetFunction.Match(Date - 1,
TargetWkbook.Worksheets(LegendSt).Range("$E$2:$E$2 1").Value, 1)

Then the answer I get is 15. Am not able to figure out why?

Could somebody please guide me.

Regards,
HP
India