Returns the position of an item in a list.
Thanks to Chad Langhans for the contribution.

'sLookupValue - The value you want to find.
'rgeLookupArray - The contiguous range of cells containing possible lookup values.

Public Function FINDMATCH(ByVal sLookupValue As String, _
                          ByVal rgeLookupArray As Range) As Integer
Dim ilow As Integer
Dim ihigh As Integer
Dim imiddle As Integer

   ilow = 0
   ihigh = rgeLookupArray.Cells.Count
   Do While ilow <= ihigh
      imiddle = (ilow + ihigh) / 2
      If VBA.StrComp(sLookupValue, rgeLookupArray.Cells(imiddle, 1).Value, _
                     VbCompareMethod.vbTextCompare) = 0 Then
         FINDMATCH = imiddle
         Exit Function
      End If
      If VBA.StrComp(sLookupValue, rgeLookupArray.Cells(imiddle, 1).Value, _
                     VbCompareMethod.vbTextCompare) = -1 Then
         ihigh = imiddle - 1
         ilow = imiddle + 1
      End If
   Call MsgBox("This entry has not been found")
End Function

The "rgeLookupArray" must be a single block of cells, either in a row or a column.
This function uses a binary search and is very similar to the MATCH function.

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext