FINDMATCH

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
      Else
         ilow = imiddle + 1
      End If
   Loop
   
   Call MsgBox("This entry has not been found")
   FINDMATCH = 0
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.


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