☰

Autocompletar personalizado de Excel

tag-icon • tag-icon microsoft-excel autocomplete
Autocompletar personalizado de Excel

Tengo una lista larga en una hoja de mi archivo de Excel:

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

En otra hoja, necesito ingresar esos números. Pero como no puedo recordar qué número pertenece a qué texto, me gustaría tener algún tipo de función de autocompletar que me muestre qué valor pertenece a qué número. Por ejemplo, si escribo 12, me gustaría ver una información sobre herramientas que muestra lo siguiente:

123 - Text 123
127 - Another text

porque ambos 123y 127comienzan con 12.

¿Es posible algo así? No importa si es posible con la funcionalidad integrada o creando un complemento o un script VBA.

Respuesta1

Resultados

Esto es lo que se puede hacer con VBA (¡no con VBScript!). Crea automáticamente el comentario cada vez que se cambia el valor de la celda.

Resultados

Código

Rango de fuente

Para que el código funcione es necesario crear unrango con nombre Sourcepara sus datos de origen.

ingrese la descripción de la imagen aquí

Módulo de código estándar

Necesitará crear un módulo de código estándar en el editor de Visual Basic ( Alt+ F11) para las dos funciones necesarias para lograr esto. Microsoft Scripting RuntimeTambién se debe establecer una referencia a .

Referencias

Pegue el siguiente código en su módulo de código estándar.

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 supone que los Números se encuentran en la Columna 1 y los Valores en la Columna 2; también omitirá la primera fila. Puede modificarlo para que se adapte mejor a sus necesidades.

Código de hoja de trabajo

Pegue el siguiente código en su código de hoja de trabajo

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

Puede cambiar el Me.Rangea cualquier celda que desee.

Uso

Simplemente ingrese un valor en la celda que especificó y el comentario que contiene todas las coincidencias se agregará automáticamente.

Preocupaciones

Este código crea el diccionario cada vez que busca coincidencias; no es gran cosa con Sourcerangos de tamaño pequeño a mediano (lo probé hasta 10,000 y aún así se ejecutó en unos pocos milisegundos).

El comentario tiende a salirse de la pantalla si hay muchas coincidencias; la única forma real de solucionarlo es ingresar un valor más específico.

Las celdas en blanco en el Sourcerango se ignoran mediante el uso xlCellTypeConstants; esto no funcionará para las fórmulas; deberá cambiar xlCellTypeFormulaso encontrar otra forma de seleccionar solo las celdas con valores.

información relacionada