Hi there

Using Excel 2003

In VBA I have a one-dimensional array of dates called dateslist() and
wish to test what position in this array a single date value (called
jdate) is at.

To do this I am using the following in my code:

datepos = Application.WorksheetFunction.Match(jdate, dateslist, 0)

BUT, it keeps returning an error "Run-time error 1004: Unable to get
the Match property of the WorksheetFunction class"

Anybody know how to fix this please? Or a better way to get the
answer I am seeking?

Many Thanks
Lloyd

On 23 Jul, 17:07, pinkfloydfan wrote:
Hi there

Using Excel 2003

In VBA I have a one-dimensional array of dates called dateslist() and
wish to test what position in this array a single date value (called
jdate) is at.

To do this I am using the following in my code:

datepos = Application.WorksheetFunction.Match(jdate, dateslist, 0)

BUT, it keeps returning an error "Run-time error 1004: Unable to get
the Match property of the WorksheetFunction class"

Anybody know how to fix this please? Or a better way to get the
answer I am seeking?

Many Thanks
Lloyd

Lloyd,

The only way that I know is to loop through the array to find the
value you are looking for

e.g. where option base is 0:

For i = 0 To UBound(datelist)
If datelist(i) = jdate Then datepos = i
Next i

hth

Toyin.

Thanks Toyin

For the moment I have implemented a While...Wend loop and used the
value to get out the final result I need from a second array (Array2):

i = 1
While dateslist(i) < jdate
i = i + 1
Wend

if dateslist(i)<jdate then
result = 0
else
result = Array2(i)
end if

But, if anyone has a quicker way of doing it that would be great

Lloyd

Works fine in xl2002. There must be something odd about jdate.

Alan Beban

wrote:
On 23 Jul, 17:07, pinkfloydfan wrote:
Hi there

Using Excel 2003

In VBA I have a one-dimensional array of dates called dateslist() and
wish to test what position in this array a single date value (called
jdate) is at.

To do this I am using the following in my code:

datepos = Application.WorksheetFunction.Match(jdate, dateslist, 0)

BUT, it keeps returning an error "Run-time error 1004: Unable to get
the Match property of the WorksheetFunction class"

Anybody know how to fix this please? Or a better way to get the
answer I am seeking?

Many Thanks
Lloyd

Lloyd,

The only way that I know is to loop through the array to find the
value you are looking for

e.g. where option base is 0:

For i = 0 To UBound(datelist)
If datelist(i) = jdate Then datepos = i
Next i

hth

Toyin.

If IsError(Application.Match(jdate, arr, 0)) Then
result = 0
Else
result = Application.Match(jdate, arr, 0)
End If

Alan Beban

pinkfloydfan wrote:
Thanks Toyin

For the moment I have implemented a While...Wend loop and used the
value to get out the final result I need from a second array (Array2):

i = 1
While dateslist(i) < jdate
i = i + 1
Wend

if dateslist(i)<jdate then
result = 0
else
result = Array2(i)
end if

But, if anyone has a quicker way of doing it that would be great

Lloyd

The message is telling you that there is no Match function having the
argument types associated with the data types of jdate and dateslist.

datepos = Application.WorksheetFunction.Match(jdate, dateslist, 0)

What are the data types of jdate and dateslist?