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?

 

[NdNoviceHlp]

I'll give it a go...
module code...

Public Function SplitFn(StrArr As String, Delim As String, Num As Integer) As Integer
Dim Arr As Variant, Cnt As Integer, Last As Integer, TempStr As String
'remove array brackets
TempStr = Right(Left(StrArr, Len(StrArr) - 1), Len(Left(StrArr, Len(StrArr) - 1)) - 1)
Arr = Split(TempStr, Delim)
For Cnt = LBound(Arr) To UBound(Arr)
If Arr(Cnt) = Num Then
Last = Cnt + 1 'option base 1
End If
Next Cnt
SplitFn = Last
End Function

Spreadsheet formula...

=SplitFn("{12,,12,0,,12,0,,}",",",12)

HTH. Dave