Index of last element in array VBA using Application.Match

Question

Consider the following piece of VBA code, which generates an array with only two elements, searching for the latter of the two elements. The two commented lines will add a third entry to the array. This produces the output

Error 2042

in the Immediate window. If I change the script to a three element array, and try to Match the second element, I get the proper index in return. If I, however, search for the last element, I get the same error.

Why is this happening?

Working MWE

Sub testArray()
Dim myArray() As Variant
' ReDim myArray(1 To 2)
ReDim myArray(1 To 3)
myArray(1) = "Trondheim"
myArray(2) = "Levanger"
myArray(3) = "Dummy"

Dim Index As Variant
Dim test As Variant

test = "Levanger"
Index = Application.Match(test, myArray)
Debug.Print Index
End Sub

Minimal non-working example

Sub testArray()
Dim myArray() As Variant
' ReDim myArray(1 To 2)
ReDim myArray(1 To 3)
myArray(1) = "Trondheim"
myArray(2) = "Levanger"
myArray(3) = "Dummy"

Dim Index As Variant
Dim test As Variant

'test = "Levanger"
test = "Dummy"
Index = Application.Match(test, myArray)
Debug.Print Index
End Sub

Is this in Access? I cant get the code to work in Excel (no Application.Match) — NickSlash
– NickSlash, Commented Nov 18, 2015 at 13:59
For extra clarity, could you paste up the code in the not-working state? — Bathsheba
– Bathsheba, Commented Nov 18, 2015 at 13:59
I think that match is expecting a 2-D array (which is how ranges are represented) and doesn't know how to handle a pointer to a 1-D array. The underlying C/C++ code must be trying to treat it as a 2-D array (just a guess). — John Coleman
– John Coleman, Commented Nov 18, 2015 at 14:04
@JohnColeman you can use a single dimension array with Match - for example Application.Match(3, Array(2, 3, 4), 0) will work — SierraOscar
– SierraOscar, Commented Nov 18, 2015 at 14:21

mielk · Accepted Answer · 2015-11-18 14:19:47Z

2

Give 0 as optional parameter match_type:

Index = Application.Match(test, myArray, 0)

It causes that the function will return the exact match.

According to the Excel help, if this parameter is omitted, by default match_type = 1 is used.

In that case "MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE."

edited Nov 18, 2015 at 14:19

answered Nov 18, 2015 at 14:15

mielk

3,94014 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Holene Over a year ago

Could you please elaborate why this solves the problem, and why it occures? Is @John Coleman right?

mielk Over a year ago

@Holene No, he's not right, since as you said, the function works properly for second item if there are three items in your array - that means it accepts 1D arrays. I edited my answer to explain it. If you try the same function in Excel worksheet it will give you the same error for third item.

user4039065 Over a year ago

@Holene - For both VLOOKUP and MATCH the data must be sorted if anything but 0 or FALSE is specified. For MATCH this could be ascending or descending depending upon whether -1 or 1 is specified for the range_lookup parameter.

David Zemens Over a year ago

Note that since Application.Match can return an error type, OP may want to do some error-handling in case the match isn't found. But otherwise, good answer + explanation!!

user4039065 Over a year ago

@Holene - Strictly speaking, if the values were in ascending order then it should work reliably without the 0 parameter. However, since that was not intentional there is no guarantee that the ascending order would be maintained so any correct result can only be considered coincidental.

|

SierraOscar · Accepted Answer · 2015-11-18 14:52:33Z

You need to specify an exact match type, as the strings are not compared as actual text:

Example 1: No argument supplied

Sub MM()

Dim myArray As Variant

myArray = Array("test", "best", "rest")

Index = Application.Match("test", myArray)
Debug.Print Index    '// Prints "3"

Index = Application.Match("rest", myArray)
Debug.Print Index    '// Also prints "3"

End Sub

The default match type argument is 1 - Less Than which appears to implicilty evalute each item on it's code value rather than it's text value - even when Option Compare statements are used. For this reason it is always advised to use these type of function with data that has already been sorted.

This kind of makes sense as there has to be a numerical value derived at some level in order for a 'less than' comparison to occur.

Example 2: Match Argument Supplied

Sub MM()

Dim myArray As Variant

myArray = Array("test", "best", "rest")

Index = Application.Match("test", myArray, 0)
Debug.Print Index    '// Prints "1"

Index = Application.Match("rest", myArray, 0)
Debug.Print Index    '// Prints "3"

End Sub

As you can see - this gives the expected output. We have supplied the match argument 0 - Exact Match which when supplied with a valid lookup value will return the correct index whether comparing on a binary or text basis because the values are identical.

Collectives™ on Stack Overflow

Index of last element in array VBA using Application.Match

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related