[RESOLVED] Working with Array VBA Excel

[billhuard]

Is there a faster way to find the index of a value contained in an array than looping the entire array and compare the value you want to find to the value of the index???

EX:

For lnCounter = 0 To UBound(TheArray, 2)
    If MyValue = TheArray(lnCounter,1) then
        MyIndex = lnCounter
    End If
Next lnCounter

Thanks

[DKenny]

No there isn't a better way, but you could optimize your code by including an EXIT FOR statement within your IF...THEN. That way you only loop through the array until you find the value.

For lnCounter = 0 To UBound(TheArray, 2)
    If MyValue = TheArray(lnCounter, 1) Then
        MyIndex = lnCounter
        Exit For
    End If
Next lnCounter

[Ecniv]

If your array is already sorted you can do the search which moves by halves.

ie
1
2
3
4
5
6
7


You look for 5.
The program first moves to the half way point (4) says its looking for greater than this.
It then looks at the next 4, divides in half again, and checks 6
The program now says its looking for lower, so between positions 4 and 6.
Then it halves the remaining list items and returns 5.

There are other methods too.

[billhuard]

thanks for the advise