☰

Preenchimento automático personalizado do Excel

tag-icon • tag-icon microsoft-excel autocomplete
Preenchimento automático personalizado do Excel

Eu tenho uma longa lista em uma planilha no meu arquivo 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 tipo de recurso de preenchimento automático, que me mostrasse qual valor pertence a qual número. Por exemplo, se eu digitar 12, gostaria de ver uma dica que mostre o seguinte:

123 - Text 123
127 - Another text

porque ambos 123e 127começam com 12.

Algo assim é possível? Não importa se é possível com funcionalidade integrada ou criando um suplemento ou script VBA.

Responder1

Resultados

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

Resultados

Código

Faixa de origem

Para que o código funcione você precisa criar umintervalo nomeado Sourcepara seus dados de origem.

insira a descrição da imagem aqui

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 fazer isso. Uma referência para Microsoft Scripting Runtimetambém deve ser definida.

Referências

Cole o seguinte código em seu módulo de código padrão.

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

Este código assume que os Números são encontrados na Coluna 1 e os Valores na Coluna 2 - ele também pulará a primeira linha. Você pode ajustá-lo para melhor atender às suas necessidades.

Código da planilha

Cole o seguinte código no código da sua 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 Me.Rangepara 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 procura correspondências - não é grande coisa com Sourceintervalos de tamanho pequeno a médio (testei até 10.000 e ainda foi executado em alguns milissegundos).

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

As células em branco no Sourceintervalo são ignoradas usando xlCellTypeConstants, isso não funcionará para fórmulas - você precisará mudar para xlCellTypeFormulasou encontrar outra maneira de selecionar apenas as células com valores.

informação relacionada