任何人都可以改善下面的VBA的Fuzzyfind功能？ [英] Can anyone improve on the below Fuzzyfind function for VBA?

问题描述

这个功能可以让您从一个范围内找到相似的字符串，而无需进行精确的搜索。

公式如下：= FuzzyFind（A1，B $ 1：B $ 20）
假设您正在搜索的字符串是A1
和你的引用或选项表是B1：B20

代码在这里：

  Function FuzzyFind（lookup_value As String，tbl_array As Range）As String 
 Dim i As Integer，str As String，Value As String 
 Dim a As Integer，b As Integer，cell As变量
对于每个单元格tbl_array 
 str = cell 
 For i = 1 To Len（lookup_value）
如果InStr（cell，Mid（lookup_value，i，1））> 0然后
 a = a + 1 
 cell = Mid（cell，1，InStr（cell，Mid（lookup_value，i，1）） -  1）& Mid（cell，InStr（cell，Mid（lookup_value，i，1））+ 1,9999）
 End If 
 Next i 
a = a  -  Len（cell）
 If a> b然后
b = a 
值= str 
结束如果
a = 0 
下一个单元格
 FuzzyFind =值
结束函数
  

这个函数的结果是错过的。任何人都可以提高这个算法的智能吗？

谢谢：）

解决方案

它会找到最好的匹配
$ b $ pre $函数FuzzyFind2（lookup_value As String，tbl_array As Range）As String
Dim i As整数，str作为字符串，值作为字符串
昏暗a作为整数，b作为整数，单元格作为变量
昏暗发现作为布尔

b = 0
对于每个单元格在tbl_array
str = cell
i = 1
找到=真
找到时=真
找到=假
如果InStr（i，str，lookup_value ）> 0然后
a = a + 1
找到=真
i = InStr（i，str，lookup_value）+ 1
End If
Loop

如果a> b然后
b = a
Value = str
End If
a = 0
Next cell
FuzzyFind2 = Value
End Function

This function lets you find similar strings from a range without having to do an exact search.

The formula looks like this: =FuzzyFind(A1,B$1:B$20) assuming the string you are performing a search for is in A1 and your reference or options table is B1:B20

The code is here:

Function FuzzyFind(lookup_value As String, tbl_array As Range) As String
Dim i As Integer, str As String, Value As String
Dim a As Integer, b As Integer, cell As Variant
For Each cell In tbl_array
  str = cell
  For i = 1 To Len(lookup_value)
    If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then
      a = a + 1
      cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999)
    End If
  Next i
  a = a - Len(cell)
  If a > b Then
    b = a
    Value = str
  End If
  a = 0
Next cell
FuzzyFind = Value
End Function

The results from this function are hit and miss. Can anyone improve the intelligence of this algorithm?

Thank you :)

解决方案

Try this out, I think it will find the best match

Function FuzzyFind2(lookup_value As String, tbl_array As Range) As String
Dim i As Integer, str As String, Value As String
Dim a As Integer, b As Integer, cell As Variant
Dim Found As Boolean

b = 0
For Each cell In tbl_array
  str = cell
  i = 1
  Found = True
  Do While Found = True
    Found = False
    If InStr(i, str, lookup_value) > 0 Then
        a = a + 1
        Found = True
        i = InStr(i, str, lookup_value) + 1
    End If
  Loop

  If a > b Then
    b = a
    Value = str
  End If
  a = 0
Next cell
FuzzyFind2 = Value
End Function

这篇关于任何人都可以改善下面的VBA的Fuzzyfind功能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！