☰

Excel 自定义自动完成

tag-icon • tag-icon microsoft-excel autocomplete
Excel 自定义自动完成

我的 Excel 文件中有一个长列表：

Number   Value
123      Text 123
127      Another text
131      Yet another text
...      ...

在另一张表上，我需要输入这些数字。但由于我记不住哪个数字属于哪个文本，所以我希望有某种形式的自动完成功能，可以向我显示哪个值属于哪个数字。例如，如果我输入12，我希望看到一个工具提示，向我显示以下内容：

123 - Text 123
127 - Another text

因为和都123以127开头12。

这可能吗？无论使用内置功能还是通过创建插件或 VBA 脚本都可以。

答案1

结果

这是用 VBA（不是 VBScript！）可以做到的。每当单元格值发生变化时，它都会自动创建注释。

结果

代码

源范围

为了使代码正常运行，您需要创建一个命名范围 Source作为源数据。

在此处输入图片描述

标准代码模块

您需要在 Visual Basic 编辑器 ( Alt+ F11) 中为实现此目的所需的两个函数创建一个标准代码模块。Microsoft Scripting Runtime还必须设置对的引用。

参考

将以下代码粘贴到标准代码模块中。

Option Explicit

Function GetMatches(strInput As String) As String
    Dim dict As Scripting.Dictionary
    Dim key As Variant
    Dim strOutput As String

    strOutput = "Matches found:" & vbCrLf

    Set dict = GenerateDictionary()

    For Each key In dict.Keys
        If key Like strInput & "*" Then strOutput = _
            strOutput & vbCrLf & key & " - " & dict(key)
    Next

    GetMatches = strOutput
    Set dict = Nothing
End Function

Private Function GenerateDictionary() As Scripting.Dictionary
    Dim source As Range
    Dim cell As Range
    Dim dict As New Scripting.Dictionary
    Dim number As Integer
    Dim value As String

    Set source = Range("Source").SpecialCells(xlCellTypeConstants)

    For Each cell In source
        If cell.Row < 2 Then GoTo PassRow
        If cell.Column = 1 Then number = cell.value
        If cell.Column = 2 Then value = cell.value
        If number <> 0 And value <> "" And cell.Column = 2 Then _
            dict.Add number, value
PassRow:
    Next

    Set GenerateDictionary = dict
    Set dict = Nothing
End Function

此代码假设数字位于第 1 列，值位于第 2 列 - 它还将跳过第一行。您可以对其进行调整以更好地满足您的需求。

工作表代码

将以下代码粘贴到工作表代码中

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strInput As String

    If Not Intersect(Target, Me.Range("D1")) Is Nothing Then
        strInput = Target.value
        Target.ClearComments
        Target.AddComment (GetMatches(strInput))
        Target.Comment.Shape.TextFrame.AutoSize = True
    End If
End Sub

您可以将更改为Me.Range您想要的任何单个单元格。

用法

只需在指定的单元格中输入一个值，就会自动添加包含所有匹配项的注释。

担忧

此代码每次搜索匹配时都会创建字典 - 对于小到中等大小的Source范​​围来说这不是什么大问题（我测试了它最多 10.000 个，它仍然在几毫秒内执行）。

如果有很多匹配，评论就会倾向于溢出屏幕 - 解决这个问题的唯一真正方法是输入更具体的值。

Source使用 会忽略范围内的空白单元格xlCellTypeConstants，这对公式不起作用 - 您需要切换到xlCellTypeFormulas或找到另一种仅选择具有值的单元格的方法。

相关内容