CODEWITHSUNDEEP
arraysvbaexcel
lakshmen
lakshmen

Reputation: 29094

Getting the index of item in an array VBA

I am trying to get the index of the item in an array VBA, but I have issue getting it.

Const NoOfVol As Integer = 5
Dim vol(NoOfVol) As Integer
For Index = 1 To NoOfVol
   vol(Index) = Cells(15 + Index, 8).Value
Next

The array looks like this: (-2500,-1250,0,1250,2500). I am calling the function this way.

Function Find(ByVal Value As Variant, arr As Variant) As Integer
    Find = Application.Match(Value, arr, False)
End Function
posOfVol = Find(-1250, vol)

But the posOfVol = 3, when it should be 2. Not sure where my error is. Need some guidance on this.

Upvotes: 1

Views: 6341

Answers (1)

Rory
Rory

Reputation: 34075

Your array is actually effectively declared as:

Dim vol(0 to NoOfVol) As Integer

unless you have an Option Base 1 statement. Since your loop goes from 1 to 5, you never populate the first element of the array and it retains its default value of 0. Therefore your array is actually:

(0,-2500,-1250,0,1250,2500)

and since you are looking for an exact match, -1250 is found at the third position.

Upvotes: 1

Related Questions