Match in VBA

[Hari]

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$21").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 Guillett]

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

 

[Hari]

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]

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

 

[Jim Cone]

Hari,
Try it without ".Value"

 

[Hari]

Hari,
Try it without ".Value"

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

 

[Don Wiss]

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$21"), 1)

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

 

[Hari]

Is this what you tried?

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

Yes......

Regards,
HP
India