我会将宏放在您的个人部分中,这样宏就可以在所有工作表中使用。通过录制虚拟宏并选择将其存储在个人宏工作簿中来执行此操作。现在您可以在此个人工作簿中手动添加新的宏和函数。
我刚刚尝试了这个(不知道原始来源),效果很好。
公式如下所示: =PERSONAL.XLSB!FuzzyFind(A1,B$1:B$20)
代码在这里:
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