title: Index of last element in array VBA using Application.Match
body: <p>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</p>
  
  <blockquote>
    <p>Error 2042</p>
  </blockquote>
  
  <p>in the Immediate window. If I change the script to a three element array, and try to <code>Match</code> the second element, I get the proper index in return. If I, however, search for the last element, I get the same error. </p>
  
  <p>Why is this happening?</p>
  
  <p>Working MWE</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Minimal non-working example</p>
  
  <pre><code>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
  </code></pre>