Getting a the position of a single item in an array
 

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

Getting a the position of a single item in an array
 

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.

Getting a the position of a single item in an array
 

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

Getting a the position of a single item in an array
 

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.

Getting a the position of a single item in an array
 

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

Getting a the position of a single item in an array
 

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?