Application.match - apparent error?

[katem]

OK, this is a strange one. I'm trying to use application.match to
find a value in an array and it's giving me weird results. I'm
reading in a date as the lookup value (7/06/2007 0:13) and it's
looking through an array full of dates (sorted in ascending order) to
find the nearest lowest value:-

matchArray() =
Worksheets("Adamstown").Range("a2:a1027").Value

startArray = WorksheetFunction.Match(controlArray(i, 2),
matchArray, -1)

however, no matter what the lookup value is (in controlArray(i,2))
startArray always seems to be 440 (which isn't the right answer (440
is a date of 9/06/2007 23:56 - this is the last record for the day).
The correct answer is 2. I've input

(the dates range from 7/06/2007 0:03 to 13/06/2007 23:53) (Australian
Date System)

Can anyone help me understand why this doesn't work & how I can fix
it. I've got around it by using the match function on the worksheet
(which is giving me the correct answer) and reading this number into
startArray. However, this is an ugly solution and I'd prefer if it
didn't have to run this way.

I've tested it with different values, below and above the 9/06/2007
23:56 and the answer is always 440. If the search value is outside
the lookup range, I'm still getting 440.

i've also tried both the worksheet.match and application.match
functions, both give the same result.

If someone can give me a pointer, I can upload the file somewhere it
can be looked at, if this helps.

Thanks for your help!
Kate

 

[Joel]

make sure controlArray(i,2) is a date value. You may need to add

datevalue(controlArray(i,2))

 

[Jim Cone]

Kate,

A "-1" match type requires the data to be sorted in descending order.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"katem" <[email protected]>
wrote in message
OK, this is a strange one. I'm trying to use application.match to
find a value in an array and it's giving me weird results. I'm
reading in a date as the lookup value (7/06/2007 0:13) and it's
looking through an array full of dates (sorted in ascending order) to
find the nearest lowest value:-

matchArray() = Worksheets("Adamstown").Range("a2:a1027").Value
startArray = WorksheetFunction.Match(controlArray(i, 2), matchArray, -1)
-snip-
Thanks for your help!
Kate

 

[katem]

The plot thickens....

I put in a value which was in the list to match (so an exact match
would work) and it brings back 0 for every value I've tried in the
list (whether on not it's in the list). And if I run a +1 match, I
always get 1026 (which is the upperbound of the array).

I've tried it with and without the 'datevalue'.

Any other guesses as to what's up?
Thanks for your help,
Kate

 

[Jim Cone]

I don't understand your answer as you don't reference the data sort order.

If match type is -1 the data must be in descending order.
If match type is +1 the data must be in ascending order
If match type is 0 (exact match) the data can be in any order.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"katem" <[email protected]>
wrote in message
The plot thickens....

I put in a value which was in the list to match (so an exact match
would work) and it brings back 0 for every value I've tried in the
list (whether on not it's in the list). And if I run a +1 match, I
always get 1026 (which is the upperbound of the array).

I've tried it with and without the 'datevalue'.
Any other guesses as to what's up?
Thanks for your help,
Kate

 

[katem]

Sorry - that was in my first post, should've made myself more clear.
The list is dates in ascending order, so I think +1 or 0 should work.
But neither of them are behaving.

Any guesses?
Thanks for your help!
Kate

 

[Niek Otten]

I suspect one of your dates (either the one to be looked up or in the table) is text and te other one(s) isn't.
You can easily test that with the ISTEXT function..
Just re-formatting as number or date doesn't help' you have re-format AND to re- enter (F2, ENTER) or multiply by 1 to get nembers
(dates).

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Sorry - that was in my first post, should've made myself more clear.
| The list is dates in ascending order, so I think +1 or 0 should work.
| But neither of them are behaving.
|
| Any guesses?
| Thanks for your help!
| Kate
|
| > I don't understand your answer as you don't reference the data sort order.
| >
| > If match type is -1 the data must be in descending order.
| > If match type is +1 the data must be in ascending order
| > If match type is 0 (exact match) the data can be in any order.
| > --
| > Jim Cone
| > San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
| >
| > "katem" <[email protected]>
| > wrote in message
| > The plot thickens....
| >
| > I put in a value which was in the list to match (so an exact match
| > would work) and it brings back 0 for every value I've tried in the
| > list (whether on not it's in the list). And if I run a +1 match, I
| > always get 1026 (which is the upperbound of the array).
| >
| > I've tried it with and without the 'datevalue'.
| > Any other guesses as to what's up?
| > Thanks for your help,
| > Kate
|
|

 

[katem]

Hey - just checked and both the match array and number being read in
both are numbers. When they are in the VBA watch window, the dates
have a # on either side and the istext function reads 'false' for
both, so I don't think that's what's causing the issue. If you can
tell me where I can upload, I can send the file to somewhere for
people to look at it.

I also tried it on another computer this morning and am getting the
same results.

Thanks so much for your help - any other ideas?
Cheers,
Kate

 

[Niek Otten]

You can send me the file

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hey - just checked and both the match array and number being read in
| both are numbers. When they are in the VBA watch window, the dates
| have a # on either side and the istext function reads 'false' for
| both, so I don't think that's what's causing the issue. If you can
| tell me where I can upload, I can send the file to somewhere for
| people to look at it.
|
| I also tried it on another computer this morning and am getting the
| same results.
|
| Thanks so much for your help - any other ideas?
| Cheers,
| Kate
|
| > I suspect one of your dates (either the one to be looked up or in the table) is text and te other one(s) isn't.
| > You can easily test that with the ISTEXT function..
| > Just re-formatting as number or date doesn't help' you have re-format AND to re- enter (F2, ENTER) or multiply by 1 to get
nembers
| > (dates).
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | Sorry - that was in my first post, should've made myself more clear.
| > | The list is dates in ascending order, so I think +1 or 0 should work.
| > | But neither of them are behaving.
| > |
| > | Any guesses?
| > | Thanks for your help!
| > | Kate
| > |
| > | > I don't understand your answer as you don't reference the data sort order.
| > | >
| > | > If match type is -1 the data must be in descending order.
| > | > If match type is +1 the data must be in ascending order
| > | > If match type is 0 (exact match) the data can be in any order.
| > | > --
| > | > Jim Cone
| > | > San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
| > | >
| >
| > | > The plot thickens....
| > | >
| > | > I put in a value which was in the list to match (so an exact match
| > | > would work) and it brings back 0 for every value I've tried in the
| > | > list (whether on not it's in the list). And if I run a +1 match, I
| > | > always get 1026 (which is the upperbound of the array).
| > | >
| > | > I've tried it with and without the 'datevalue'.
| > | > Any other guesses as to what's up?
| > | > Thanks for your help,
| > | > Kate
| > |
| > |
|
|