VBA Application.Match not working.

Hi,

. I ran into a mysterious ( for me ) problem in a code written for an OP
( http://www.excelforum.com/excel-prog...ml#post4156568
)
. In brief .Match ( or . WorksheetFunction.Match ) was not working as I expected.

. I got the code to work by replacing the .Match bit with a simple Loop, but i am very keen to clear up the anomaly.
. I have stripped the code down to the minimum to help me explain and to aid anyone kind enough to help

. The Problem / requirement.
. – I have an ordered ( in column C ) and an un ordered ( in column F ) list of Reference Numbers , thus:

Using Excel 2007

Row\Col	A	B	C	D	E	F	G
1		Correct Order			Incorrect Order
2		2	405C			405C
3		3	406B			408Q
4		4	407D			407D
5		5	408Q			406B
6		6	405P			405P
7		7	403A			403A
8

Mark L

......
. As part of my larger code I required to produce an Array ( rws() )full with the indicies taken from column B, but reordered in an order to reflect the disorder of the un ordered column.

. To demonstrate this pictorially I reproduce the contents of this array in column E, as follows:
. –1) Firstly I produce the results using part 3a) of the demonstration code I give at the end of this Post. This uses a simple Loop with another loop nested within in it to go through each reference number in column C and then for that reference number go through each reference number in column F, and when a Match is obtained the indicia ( given by the outer Loop Bound variable Count, rwM ) is given to the Array ( rws() ) in the position in that Array column given by the Inner loop Bound variable Count , ( rwM ) correspond to the position of the current reference number being considered from column F. – That was a mouthful... A picture paints a thousand word: Results after running part 3a) of the code

Using Excel 2007

Row\Col	B	C	D	E	F
1	Correct Order			Incorrect Order
2	2	405C		2	405C
3	3	406B		5	408Q
4	4	407D		4	407D
5	5	408Q		3	406B
6	6	405P		6	405P
7	7	403A		7	403A

Mark L

.. As you see - good old fashioned looping with Array works!!!!!

..............
. – 2) Now secondly ...... as I try to break my old habits and use new Methods I attempt in part 3b) of the code to replace the inner loop with the .Match Method – As I understand its most basic form the .Match is designed to do exactly the requirement of the inner nested loop,- That is to say based on a string first argument, it searches for that string in the List given in the second argument , and having found it returns a number ( indicia) corresponding to the position in the list off that first argument string )
. Using for example, the test data above, code part 3b) gets this far (returning incorrect indices ) before crashing:

Using Excel 2007

Row\Col	B	C	D	E	F
1	Correct Order			Incorrect Order
2	2	405C		2	405C
3	3	406B		2	408Q
4	4	407D		4	407D
5	5	408Q		7	406B
6	6	405P		2	405P
7	7	403A			403A

Mark L

.

Can anyone please explain what is going wrong with the .Match method in part 3b).

Thanks,
Alan

Here is the test code I have been using, ( and I also upload the test File with the macro in the first sheet module )

Please Login or Register  to view this content.

You forgot the Match argument in the Match-function

Please Login or Register  to view this content.

Originally Posted by bakerman2

You forgot the Match argument in the Match-function

Please Login or Register  to view this content.

Hi bakerman2
. Thanks for the quick reply...

...Ahh, that simple..how very stupid of me...
. For my sins I will Google to death that 3rd argument until I understand it. – Initially I see the 0 means look for the first exact match, so that makes sense. But I cannot understand yet from the explanations I have found so far what the option 1 or -1 does?,
. I am still a beginner with these things.. can you give a simple explanation in plain English what that 3rd argument is about, especially the option 1 and -1 ?

Thanks again.
Alan

Values of 1 and -1 tell Match that the array is in ascending or descending order respectively. Whether that's true or not (and sometimes it's convenient to lie), Match performs a binary search, which it can do in O(log(n)) time.

A value of 0 tells Match that the array is unordered, and so it does a linear search, which takes O(n) time.

@ AB33,
Hi,
. That info you gave was , or was very similar to, the first thing I googled..(in German)
https://msdn.microsoft.com/de-de/lib.../Ff835873.aspx
. I did not immediately follow it, - I think it is one of those things that you need to understand it and then such an example makes sense the second time around. But it was very helpful to have it up on the screen as I prepared this post. There appears also to be a little bit more info in what you produced compared with what comes up by me in the VBA help (F1). So thanks very much for taking the time to reproduce it for me.. - saved me a bit of time
Alan
..................................
@ shg,

Originally Posted by shg

Values of 1 and -1 tell Match that the array is in ascending or descending order respectively. Whether that's true or not (and sometimes it's convenient to lie....

.. sounds like a handy thing to have in mind, Thanks.

Originally Posted by shg

..... Match performs a binary search, which it can do in O(log(n)) time.
A value of 0 tells Match that the array is unordered, and so it does a linear search, which takes O(n) time.

... ahh.. the answer I need ( because of my ignorance ) a week to try and understand. Thanks anyway for the contribution. Appreciate you taking the time
Thanks,
Alan
..................................

@ Alan..
Hi,
Ok. .. option 0 – No problem .. VBA goes through looks for a (exact match ) gives the position “along” as a long number. ( in fact looking back I have used it loads in codes, just forgot this time!! ). Crashes if no match found, so as you well know, often it is used with that Resume Next Error handler ( which is bad to do!)

. Now for the tricky stuff:

. – I learnt the articles stuff parot fashion then while jogging I came up with an idea to get it clear in my head. I think backwards starting with an example and seeing how that ties up with .Match.
. I have ( had ) a long rope in the shed which was very tangled up and so was a bit awkward to store. But I had 5 boxes. So I chopped the string into lots of random lengths between 0 – 5 meters. I wrote a “box number” from the list 1 to 5 on each box. Sorted the rope ( approximately ) in the lengths

0 - 1 , 1 – 2 , 2 – 3 , 3 – 4 , 4 – 5

And assumed I would be puting the Rope in the boxes in the “normal” sounding logical sequence corresponding to the box numbers

1 , 2 , 3 , 4 , 5 .

. VBA has a habit of guessing right what you usually want so, so i guessed the default option +1 should help. I set up a table like this..

Using Excel 2007

Row\Col	H	I	J
3	Box No.	String Length	ArrM ( For arg +1)
4	1	0-1	0
5	2	1.01-2	1.01
6	3	2.01-3	2.01
7	4	3.01-4	3.01
8	5	4.01-5	4.01
9
10		Match returned Box No.	Rope Lengths ( For arrR )
11			1.1
12			3
13			2.5
14			1.01

Mark L

.. The idea then was to use the Yellow highlighted Range as my first argument Array ( arrM() ) in the .Match fuction, and test it with the Look Up Value Third argument examples Rope lengths , and then see if I could get VBA to tell me what box the bits of rope in should be in..
.. After re reading this bit a few times

Tird Argument 1 or omitted
MATCH finds the largest value that is less than or equal to Rope lengths lookup_values. The values in the first argument lookup_array argument must be placed in ascending order

.. and playing with the numbers, i got a code up and running ( code given in next Post #8 )

Results from part 3a) of code :

Using Excel 2007

Box No.	String Length	ArrM() ( For arg +1)
1	0-1	0
2	1.01-2	1.01
3	2.01-3	2.01
4	3.01-4	3.01
5	4.01-5	4.01
	Match returned Box No.	Rope Lengths ( For arrR() )
	2	1.1
	3	3
	3	2.5
	2	1.01

Mark L

..........................................

. As for the other Third argument Option -1 ... it was all about being and going backward, so I thought I could handle that !...

... I wrote on the boxes a “backward order”

5 – 4 , 4 – 3 , 3 – 2 , 2 – 1 , 1 – 0

Corresponding to the box numbers already written on the boxes

1 , 2 , 3 , 4 , 5

. Then I set up, this Table

Using Excel 2007

Row\Col	H	I	J
10		Match returned Box No.	Rope Lengths ( For arrR() )
11			1.1
12			3
13			2.5
14			1.01
15
16	Box No.	String Length	ArrM() ( For arg -1)
17	1	4.01-5	5
18	2	3.01-4	4
19	3	2.01-3	3
20	4	1.01-2	2
21	5	0-1	1

Mark L

. Then same procedure as the last time to get a code up and running to get . Match to tell me what box I should put the bits of rope in, bearing in mind

Tird Argument -1
MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order

Results from Part 3b) of the code:

Using Excel 2007

	Match returned Box No.	Rope Lengths ( For arrR() )
	4	1.1
	3	3
	3	2.5
	4	1.01
Box No.	String Length	ArrM() ( For arg -1)
1	4.01-5	5
2	3.01-4	4
3	2.01-3	3
4	1.01-2	2
5	0-1	1

Mark L

.....
. If you follow the code in F8 debugging mode, and looking at how the arrays are filled the code explains itself.


P.s. The example is not 100 % correct, as I have rounded a bit, for example in Range I4 the correct entry would be something of the form 0-1.0099999 ... etc . depending on the actual Number type used

P.p.s.
. Opps, I needed that Rope to pull a big tree down in me Garden, F##k Now all cut up.

Updated Example File: ( XL2007 “Master_B_ggerMeRope.xlsm” )
https://app.box.com/s/p2kmdw22x3lqjdzqs0en5nsyll1v5zrk



Codes for above given below in Post # 8 ( .... I think I have it right... I will edit if I realise I got it a bit wrong.. it is a bit of a brain teaser to get the correct Look Up Table for the Array used as second argument in the .Match Fuction )

Codes for above post #7

Please Login or Register  to view this content.