copmapre and rank cells

[MikeD]

Howdy,

I would like to compare cells that I know are not exact matches. If the
cells have more than 3,4,5,x characters different I want to return a value
basically telling me how close of a match I have.

Any ideas?

thanks,
Mike

 

[Rocky McKinley]

Hi Mike,

You could try something like what I have below, however it doesn't deal with
multiple instances of strings within strings.

'The function "Compare" returns zero if Second value doesn't exist in First
value
'or if both values are equal
'Otherwise "Compare" returns the difference between the two strings lengths
'Test by sticking values in Range("A1") and Range("A2") on any worksheet

Function Compare(Rng1, Rng2) As Integer
Dim First As Variant, Second As Variant
If Len(Rng1) >= Len(Rng2) Then
First = Rng1
Second = Rng2
Else
First = Rng2
Second = Rng1
End If

If InStr(First, Second) > 0 Then
Compare = Len(First) - Len(Second)
Else
Compare = 0
End If
End Function

Sub TestIt()
MsgBox Compare(Range("A1").Value, Range("A2").Value)
End Sub

Hope this helps,
Regards, Rocky McKinley

 

[MikeD]

I'll give that a try. thanks

and I have no idea where 'copmapre' came from. Must have slipped by the
spell checker.