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

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

Don,

I actually want to find the largest date which is smaller (or equal)
than Today - 1. Hence, I have been using the argument 1.

If I use this formula in my spreadsheet it gives correct values as 6,
but If I use it within VBA it is giving as 14. Am not able to figure
out as to why this is happening.

regards,
HP
India

Hari wrote:
Don,

I actually want to find the largest date which is smaller (or equal)
than Today - 1. Hence, I have been using the argument 1.

If I use this formula in my spreadsheet it gives correct values as 6,
but If I use it within VBA it is giving as 14. Am not able to figure
out as to why this is happening.

Could somebody take a guess as to what might be wrong in my VBA
statement

regards,
HP
India

Hari,
Try it without ".Value"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Jim Cone wrote:
Hari,
Try it without ".Value"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Without . Value, the error I get is

Run time error '1004':
Unable to get the match property of the worksheet function class

Not sure as to why this is happenning.

Regards,
HP
India

On 25 Jun 2006 19:14:38 -0700, "Hari" wrote:

Jim Cone wrote:
Try it without ".Value"

Without . Value, the error I get is

Is this what you tried?

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

Don <www.donwiss.com (e-mail link at home page bottom).

Don Wiss wrote:
On 25 Jun 2006 19:14:38 -0700, "Hari" wrote:

Jim Cone wrote:
Try it without ".Value"

Without . Value, the error I get is

Is this what you tried?

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

Yes......

Regards,
HP
India