Autocompletar personalizado do Excel

Question

Eu tenho uma longa lista em uma planilha no meu arquivo do Excel:

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

Em outra planilha, preciso inserir esses números. Mas como não consigo lembrar qual número pertence a qual texto, gostaria de ter algum recurso de preenchimento automático, que mostre qual valor pertence a qual número. Por exemplo, se eu digitar 12 , gostaria de ver uma dica de ferramenta que mostre o seguinte:

123 - Text 123
127 - Another text

porque 123 e 127 começam com 12 .

É algo assim possível? Não importa se é possível com funcionalidade incorporada ou criando um script Add-In ou VBA.

    

Answer 1

Resultados

Isso é o que pode ser feito com o VBA (não o VBScript!). Cria automaticamente o comentário sempre que o valor da célula é alterado.

Código

Intervalodeorigens

Paraqueocódigofuncione,vocêprecisacriarumintervalonomeadoSourceparaseusdadosdeorigem.

Módulo de código padrão

Você precisará criar um Módulo de Código Padrão no Editor do Visual Basic ( Alt + F11 ) para as duas funções necessárias para isso. Uma referência a Microsoft Scripting Runtime também deve ser definida.

ColeoseguintecódigonoseuMódulodecódigopadrão.

OptionExplicitFunctionGetMatches(strInputAsString)AsStringDimdictAsScripting.DictionaryDimkeyAsVariantDimstrOutputAsStringstrOutput="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

Este código pressupõe que os números são encontrados na coluna 1 e os valores na coluna 2 - também ignoram a primeira linha. Você pode ajustá-lo para melhor atender às suas necessidades.

Código da planilha

Cole o seguinte código no seu código de planilha

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

Você pode alterar o Me.Range para qualquer célula que desejar.

Uso

Basta inserir um valor na célula especificada e o comentário contendo todas as correspondências será adicionado automaticamente.

Preocupações

Este código cria o dicionário toda vez que ele procura por correspondências - não é um grande problema com as faixas Source de tamanho pequeno a médio (eu testei até 10.000 e ele ainda executou em alguns milissegundos).

O comentário tende a fluir da tela se houver muitas correspondências - a única maneira real de corrigir isso é inserir um valor mais específico.

Células em branco no intervalo Source são ignoradas usando xlCellTypeConstants , isso não funcionará para fórmulas - você precisará alternar para xlCellTypeFormulas ou encontrar outra maneira de selecionar apenas as células com valores. / p>