Application.Match to find first Empty element in an array

[mse330]

Hello everyone,

Can I use Application.Match to find the first "Empty" element of an array ? Please refer to the below code, looking at the Locals window, once the array is created, all elements of the array have "Empty" value so the first debug.print give Error 2042 as I was looking for the first "Empty" element of the array. Is this possible to do with Application.Match ? Is there a different (non-loop) method to find the first "Empty" element of the array ?

The second question I have is this is a zero-based array & when looking at the results of the 2nd debug.print, it gives 1 while 1 refers to the 2nd element Arr(1) = "two". Does Application.Match works correct with 2d arrays ?

Just trying to understand the logic

Sub test()

Dim Arr(0 To 4)
Arr(0) = "one"
Arr(1) = "two"
Arr(4) = "five"

Debug.Print Application.Match(Empty, Arr, 0)
Debug.Print Application.Match("one", Arr, 0),
Debug.Print Arr(Application.Match("one", Arr, 0))

End Sub

 

[Fluff]

1) Simplest solution is to loop through the array.
2) Match gives the position in an array or range counting from 1 regardless of what base the array is.
3) No Match does not work with 2d arrays.

 

[mse330]

Thanks Fluff for your prompt reply.

So simplest solution is to loop means it is possible but complicated or it is not possible to use match with "Empty" ? Because it works nicely without loop if I was looking for a certain value as indicated in the 3rd debug.print

 

[Fluff]

You cannot match "Empty" as there is nothing in the array that says "Empty". You could try using an array version of match, but a loop would almost certainly be faster.

 

[mse330]

Thanks again Fluff for the feedback

 

[Fluff]

My pleasure.

 

[mse330]

Nicely done as always @Rick Rothstein

Edit: This was suggested by Rick

Sub test()

  Dim Arr(1 To 5)
  Arr(1) = "one"
  Arr(2) = "two"
  Arr(5) = "five"

  Debug.Print 1 + LBound(Arr) + UBound(Split(Split(Join(Arr, "|"), "||")(0), "|"))

End Sub

 

[Rick Rothstein]

Actually, I deleted the message you replied to because it did not handle the first element of the array being blank correctly. The following code does...

Dim Arr(1 To 5)
Arr(0) = "one"
Arr(1) = "two"
Arr(4) = "five"

Debug.Print LBound(Arr) - (Len(Arr(LBound(Arr))) > 0) * (1 + UBound(Split(Split(Join(Arr, "|"), "||")(0), "|")))

End Sub

 

[mse330]

Ok, I see your point ... Didn't test that part

Thank you for the solution

 

[Rick Rothstein]

Ok, I see your point ... Didn't test that part

Yeah, neither did I initially. By the way, the code I posted automatically adjusts for whatever the starting index the array is declared with (so it works with any array, not just zero-based arrays)... it gives the correct index number for the array it is evaluating.