Finding the Location within an Array

S

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
 
D

Dave Peterson

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

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
Click to expand...

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
 
Top