Getting a the position of a single item in an array

[pinkfloydfan]

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

 

[Toyin.Butler]

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.

 

[pinkfloydfan]

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

 

[Alan Beban]

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

Alan Beban

 

[Alan Beban]

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

Alan Beban

 

[gimme_this_gimme_that]

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?