Share via

VBA Function To Return The Index of an element of an array

Anonymous
2012-03-29T15:03:19+00:00

I am writing a Excel UDF function to find the index no of an element of an array. But, I encounter some technical challenges. The named range that I pass into the UDF does not have value.  I observe that vNamedrange(row, 1) = empty if I hover on  vNamedrange(row, 1).   I have been trying to solve the problem for last few days but I have not found a solution.  Can someone guide me on this challenge? Thanks in advance.

The UDF is set out below:

Background:

  • Findme = C6 = 2
  • vNamedrange = vNumlist = B5:B10 =1,2,3,4,5,6
  • The UDF return zero.

Function GetVIndex(Findme As Variant, vNamedrange As Variant) As Long

'To get the index of a named range

Dim row As Long

Dim iRows As Long

Dim Pos As Long

On Error GoTo ErrorHandler

vNamedrange = vNamedrange.Value2  'Convert Range to array

iRows = UBound(vNamedrange)

ReDim vNamedrange(1 To iRows, 1 To 1)

'Findme = Findme.Value2

For row = 1 To iRows

If Findme = vNamedrange(row, 1) Then

Pos = row

Exit For

End If

Next

GetVIndex = Pos

Exit Function

ErrorHandler:

GetVIndex = CVErr(xlErrValue)

End Function

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-03-29T16:29:27+00:00

    Does this UDF do what you want?

    Function GetIndex(FindMe As Variant, SearchMe As Range) As Variant

      GetIndex = SearchMe.Find(FindMe, SearchMe(SearchMe.Count), xlValues, _

                          xlWhole, , xlNext, False).Row - SearchMe(1).Row + 1

    End Function

    Note: The 2nd argument is a Range, not a String, so your formula would be written this way...

              =GetIndex(2, vNumlist)

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2012-03-29T15:31:13+00:00

    ReDim clears an array. To prevent that, use ReDim Preserve:

    ReDim Preserve vNamedrange(1 To iRows, 1 To 1)

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-04-05T01:33:21+00:00

    Hi, Good day! Thanks for showing me the find method. I will try it .  If I encounter new challenges, I hope you can continue guiding me.

    0 comments
  2. Anonymous
    2012-04-05T01:27:00+00:00

    Thanks.  It works.

    0 comments