I am trying to get the nth similar of a range compare to a text. The
Similarity function is to return a percentage similarity between 2 texts.
Function SimilarText(ByVal CompareText As String, _ ByRef TargetCompare As Range, _ Optional ByVal RankSimilarity As Integer = 1) As Long Dim compareResults() As Long ReDim compareResults(RankSimilarity) Dim simiResult As Single Dim smallestIndex As Integer Dim result As String Debug.Print (CompareText) For Each cell In TargetCompare simiResult = Similarity(cell.Value, CompareText) Debug.Print (simiResult) If simiResult > Application.Min(compareResults) Then smallestIndex = Application.Match(Application.Min(compareResults), compareResults, 0) - 1 Debug.Print ("Index:" & smallestIndex) compareResults(smallestIndex) = CLng(simiResult)'//This doesnt seem to do anything. I tried without the conversion but still nothing. Debug.Print ("Smallest after update:" & compareResults(smallestIndex)) '//This always 0 End If Next cell SimilarText = Application.Min(compareResults) '//So this is also alway 0 End Function
I would expect the array element will be updated after each cell like '(0.22,0.44) but the result seems to always be 0.