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 или найдите другой способ только выбора ячеек со значениями.

Источник
Другие вопросы по тегам