Finding the Location within an Array

[Sh0t2bts]

Hi All,

Thanks to this Group I was provided a snipit of code to find out is a
value is within my Array:-
If Not IsError(Application.Match(some_value, myArray,0)) Then
Do somthing
else
Do somthing else
End If

This works Great Thank You.

Is it possible to find out the location of the value without having to
loop through each entry?

Many Thanks

Mark

 

[Dave Peterson]

dim res as variant
res = application.match(some_value, myArray,0)
if iserror(res) then
'no match
else
msgbox res
end if

 

[Ron Rosenfeld]

Hi All,

Thanks to this Group I was provided a snipit of code to find out is a
value is within my Array:-
If Not IsError(Application.Match(some_value, myArray,0)) Then
Do somthing
else
Do somthing else
End If

This works Great Thank You.

Is it possible to find out the location of the value without having to
loop through each entry?

Many Thanks

Mark

You merely return the result of your Match function.

=====================================
Option Explicit
Sub foo()
Dim bar As Variant
Dim foobar
bar = Array(1, 5, 10, 15, 20)

foobar = 10

Debug.Print "Value foobar", foobar, "Location " & _
Application.WorksheetFunction.Match(foobar, bar, 0)

End Sub
==============================

Note that MATCH will return a 1's based count. So if your array is 0 based,
then the "location" within the array will be offset by 1.
--ron