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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Hari" <excel_hari@yahoo.com> wrote in message
news:1151240722.395342.36600@p79g2000cwp.googlegroups.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$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
>

[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]

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

[Jim Cone]

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

[Hari]

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

[Don Wiss]

On 25 Jun 2006 19:14:38 -0700, "Hari" <excel_hari@yahoo.com> 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$21"), 1)

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

[Hari]

Don Wiss wrote:
> On 25 Jun 2006 19:14:38 -0700, "Hari" <excel_hari@yahoo.com> 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$21"), 1)

Yes......

Regards,
HP
India