Getting Index of Last instance of an element in arrary in VBA.

[mathsbeauty]

The array for example is
array = {12,,12,0,,12,0,,}
It contains some null empty characters. Here array contains 9 elements. I want to find the index of the last occurrence of element 12. So the answer would be 6 (assuming base 1). How could it be done?

 

[Mentor82]

The array for example is
array = {12,,12,0,,12,0,,}
It contains some null empty characters. Here array contains 9 elements. I want to find the index of the last occurrence of element 12. So the answer would be 6 (assuming base 1). How could it be done?

Hi,
Try using Ubound - it returns #elements in array. The index is that value -1. Here's an example

Dim lastindex&
Dim arr
arr = {12,,12,0,,12,0,,}
lastindex= Ubound(arr)-1

 

[smozgur]

I would loop in the array starting from the last element, and look for the value:

Something like this:
Note: Array index starts from 0 as default, so I added 1 to the found index number. If starting number is mandatory, then Option Base 1 can be also used.

Sub sample()
Dim a
Dim lookFor As Integer
Dim i As Integer


    a = Array(12, Null, 12, 0, Null, 12, 0, Null, Null)
    lookFor = 12
    For i = UBound(a) To 0 Step -1
        If a(i) = lookFor Then
            MsgBox (i + 1)
            Exit For
        End If
    Next i
End Sub

 

[Mentor82]

Hi again,
Just to let you know. The array is index is count from 0 unless you set the following parameter in the mode above subs "Option Base = 1" - than ot counts from 1 on.
Within the code I wrote in previous thread I assumed that you did not set that parameter

Regards
Sebastian

 

[mathsbeauty]

Thanks for reply. If array contains only 12 and Null, could we avoid looping? It doesn't contain any other elements. I need to use this in formula.

 

[Mentor82]

Hi,
I got you wrong. Thought you wanted to get last index array but you want to get array index of last 12 occurrence. If you want to get the index of last occurrence whilst you do not have a clue where it's actually in the array, you will not avoid looping.
I would go from the last index to first. Here's an example:

Dim i&, lastindex&
Dim arr
arr= {12,,12,0,,12,0,,}
For i=Ubound(arr) to 1 step -1
    If arr(i-1)=12 then
           lastindex=i-1
           Exit for
    End if
Next element

 

[Mentor82]

Hi,
Small fix. I've written at next statement element instead of i

Dim i&, lastindex&
Dim arr
arr= {12,,12,0,,12,0,,}
For i=Ubound(arr) to 1 step -1
    If arr(i-1)=12 then
           lastindex=i-1
           Exit for
    End if
Next i

 

[steve the fish]

Hi,
Small fix. I've written at next statement element instead of i

Dim i&, lastindex&
Dim arr
arr= {12,,12,0,,12,0,,}
For i=Ubound(arr) to 1 step -1
    If arr(i-1)=12 then
           lastindex=i-1
           Exit for
    End if
Next i

The syntax for the array is incorrect. You are not checking the first or last element in the array and also you are also producing an incorrect result.

 

[Mentor82]

Hi,
The quetion is how fo you get the array? Is it a variabe and changes and where you pick it from (a rage?) or is constant and hardcoded? Here's an example of picking the array from range and checking its last index that matches value of 12.

Dim i&, lastindex&
Dim arr()
arr= range("A1:A12")
For i=Ubound(arr) to 1 step -1
    If arr(i-1)=12 then
           lastindex=i-1
           Exit for
    End if
Next i

 

[Fluff]

@Mentor82
That code cannot work, also you are still not checking the last value in the array.