Note: The other languages of the website are Google-translated. Back to English

¿Cómo copiar el formato de origen de la celda de búsqueda cuando se usa Vlookup en Excel?

En los artículos anteriores, hemos hablado de mantener el color de fondo cuando los valores de vlookup en Excel. Aquí, en este artículo, vamos a presentar un método para copiar todo el formato de celda de la celda resultante al hacer Vlookup en Excel. Haz lo siguiente.

Copie el formato de origen cuando use Vlookup en Excel con una función definida por el usuario


Copie el formato de origen cuando use Vlookup en Excel con una función definida por el usuario

Supongamos que tiene una tabla como se muestra a continuación. Ahora debe verificar si un valor especificado (en la columna E) está en la columna A y devolver el valor correspondiente con el formato en la columna C. Haga lo siguiente para lograrlo.

1. En la hoja de trabajo que contiene el valor que desea visualizar, haga clic con el botón derecho en la pestaña de la hoja y seleccione Ver código desde el menú contextual. Ver captura de pantalla:

2. En la apertura Microsoft Visual Basic para aplicaciones ventana, copie el código VBA a continuación en la ventana Código.

Código VBA 1: Vlookup y valor de retorno con formato

Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20211203
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Set xRg = Application.Range(xDicStr)
                xRg.Copy
                Range(xDic.Keys(I)).PasteSpecial xlPasteFormats
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
    Application.CutCopyMode = True
End Sub

3. Luego haga clic recuadro > Móduloy copie el código 2 de VBA a continuación en la ventana del módulo.

Código VBA 2: Vlookup y valor de retorno con formato

Public xDic As New Dictionary
'Update by Extendoffice 20211203
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = " "
        xDic.Add Application.Caller.Address, " "
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address(External:=True)
    End If
    Application.ScreenUpdating = True
End Function

4. Hacer clic en Herramientas > Referencias. Entonces revisa el Tiempo de ejecución de Microsoft Script en el cuadro Referencias - VBAProject caja de diálogo. Ver captura de pantalla:

5. presione el otro + Q llaves para salir del Microsoft Visual Basic para aplicaciones ventana.

6. Seleccione una celda en blanco adyacente al valor de búsqueda y luego ingrese la fórmula =LookupKeepFormat(E2,$A$1:$C$8,3) en el Barra de formulas, y luego presione el Enviar clave.

Nota:: En la fórmula, E2 contiene el valor que buscará, $ A $ 1: $ C $ 8 es el rango de la tabla y el número 3 significa que el valor correspondiente que devolverá se ubica en la tercera columna de la tabla. Cámbielos como necesite.

7. Siga seleccionando la primera celda de resultados y luego arrastre el controlador de relleno hacia abajo para obtener todos los resultados junto con su formato, como se muestra a continuación.


Artículos relacionados:


Las mejores herramientas de productividad de oficina

Kutools para Excel resuelve la mayoría de sus problemas y aumenta su productividad en un 80%

  • Reutilizar: Inserte rápidamente fórmulas complejas, gráficos y cualquier cosa que hayas usado antes; Cifrar celdas con contraseña; Crear lista de distribución y enviar correos electrónicos ...
  • Barra de súper fórmula (edite fácilmente varias líneas de texto y fórmulas); Diseño de lectura (leer y editar fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas sin perder datos; Contenido de celdas divididas; Combinar filas / columnas duplicadas... Prevenir celdas duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Varias celdas sin cambiar la referencia de la fórmula; Crear referencias automáticamente a varias hojas; Insertar viñetas, Casillas de verificación y más ...
  • Extraer texto, Agregar texto, Eliminar por posición, Quitar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtros a otras hojas); Orden avanzado por mes / semana / día, frecuencia y más; Filtro especial en negrita, cursiva ...
  • Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes de xls, xlsx y PDF...
  • Más de 300 potentes funciones. Compatible con Office/Excel 2007-2021 y 365. Compatible con todos los idiomas. Fácil implementación en su empresa u organización. Funciones completas Prueba gratuita de 30 días. Garantía de devolución de dinero de 60 días.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
officetab parte inferior
Comentarios (42)
Aún no hay calificaciones. ¡Sé el primero en calificar!
Este comentario fue minimizado por el moderador en el sitio
me da error de compilación, error de sintaxis

por favor ayuda
Este comentario fue minimizado por el moderador en el sitio
Buenos días,
El código ha sido actualizado en el artículo. Gracias por tu comentario.
Este comentario fue minimizado por el moderador en el sitio
También tengo el error del compilador.
Se corrige si cambia la siguiente variable con "". No ';' en el medio.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Este comentario fue minimizado por el moderador en el sitio
Hola,
Perdón por el error, el código ha sido actualizado en el artículo.
El error " " debe ser dos comillas " ". Gracias por tu comentario.
Este comentario fue minimizado por el moderador en el sitio
Tengo el mismo error.

Tendrás que cambiar el " " por real "', sin ';' como se indica a continuación
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Este comentario fue minimizado por el moderador en el sitio
Hola,
Perdón por el error, el código ha sido actualizado en el artículo. Gracias por compartir.
Este comentario fue minimizado por el moderador en el sitio
Esto es genial, gracias! El único problema es que funciona bien si estoy buscando en la misma hoja, pero no puedo hacer que funcione cuando intento hacer una búsqueda en una hoja separada a los datos de origen. Seguiré intentando
Este comentario fue minimizado por el moderador en el sitio
Julia, corrige estas líneas:
en la función LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name

en Sub Worksheet_Change:
Hojas(Dividir(xDic.Elementos(I), "|"))(1)).Rango(Dividir(xDic.Elementos(I), "|"))(0)).Copiar
Este comentario fue minimizado por el moderador en el sitio
Hola Hugo,


Tengo el mismo problema que Julia. No funciona en otras hojas. ¿Podría ayudar a escribir el código para toda la función y la hoja de trabajo secundaria? No estoy seguro de dónde reemplazar/insertar xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Nam and Sheets(Split(xDic.Items(I), "|"))(1)).Rango(Split(xDic.Items(I), "|"))(0)).Copiar


gracias a cambio
Este comentario fue minimizado por el moderador en el sitio
Agradezco mucho el seguimiento de Hugo!
Desafortunadamente, al igual que Vi, soy demasiado novato para saber dónde insertar las correcciones de código sugeridas...

Gracias de nuevo, que tengas un gran día :)
Este comentario fue minimizado por el moderador en el sitio
Hola


He intentado usar el código, pero recibo el error en la imagen adjunta. Cualquier ayuda será muy apreciada.
Este comentario fue minimizado por el moderador en el sitio
Hola,
Perdón por el error, el código ha sido actualizado en el artículo. Gracias por tu comentario.
Este comentario fue minimizado por el moderador en el sitio
Hola,

No recibo errores y realiza la búsqueda, pero debido a que mi valor de búsqueda está en otra hoja de trabajo (un escenario más probable), no extrae el formato. ¿Hay algún ajuste en el código que pueda hacer para eso? (Sea muy específico en cuanto a dónde debe ir el cambio ya que soy un novato en codificación) ¡Gracias! ¡Estoy emocionado de agregar esta función a una de mis hojas de cálculo!
Este comentario fue minimizado por el moderador en el sitio
Hola, suerte con esta pregunta, ¿cómo podemos hacer que el formato se busque en las hojas?
Este comentario fue minimizado por el moderador en el sitio
También buscando el ajuste.
Este comentario fue minimizado por el moderador en el sitio
Además, si agrego su fórmula como parte de una declaración "Si" (ver más abajo), formatea la celda como quiera LOL (o al menos eso parece. Una celda, el texto se sombreó y se puso en negrita con un borde superior en la celda; otra celda, el texto centrado)


=SI($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
Este comentario fue minimizado por el moderador en el sitio
Probé este y el que extrae solo el fondo de color y obtengo el mismo error. Error de compilación: se detectó un nombre ambiguo. Hago clic en Aceptar y se resalta xDic. ¿Alguna sugerencia? No estoy muy familiarizado con todo esto, así que por favor ayuda/explica :) gracias de antemano
Este comentario fue minimizado por el moderador en el sitio
Hola Jeni,
No olvide habilitar la opción Microsoft Script Runtime como se menciona en el paso 4.
Este comentario fue minimizado por el moderador en el sitio
Hola. Creé una hoja de cálculo en blanco y dupliqué su ejemplo en Excel 2013, pero sigo recibiendo un error de compilación: error de sintaxis y se resalta Dim I As Long. ¿Se me escapa algo? Me encantaría que esto funcione. Gracias.
Este comentario fue minimizado por el moderador en el sitio
Hola Laura,
No olvide habilitar la opción Microsoft Script Runtime como se menciona en el paso 4.
Este comentario fue minimizado por el moderador en el sitio
Hola, he estado usando el código anterior en Excel 2010 sin problemas hasta la fecha. Sin embargo, recientemente me actualicé a Office 2016 y ahora el código falla en Excel cada vez que intento completar más de una fila. Desafortunadamente, no me da un error que no sea "Microsoft Excel ha dejado de funcionar". Me preguntaba si te has encontrado con este problema anteriormente y si hay algo que deba hacer para que funcione en 2016. ¡Gracias!
Este comentario fue minimizado por el moderador en el sitio
Hola Leigh,
El código funciona bien en mi Excel 2016. Estamos tratando de actualizar el código para resolver el problema. Gracias por tu comentario.
Este comentario fue minimizado por el moderador en el sitio
Hola, gracias por el código. No recibo ningún mensaje de error, pero la fórmula solo funciona como lo haría una búsqueda virtual normal. ¿Podría por favor ayudar? Gracias por tu tiempo.
Este comentario fue minimizado por el moderador en el sitio
Hola

Tengo exactamente el mismo problema, supiste como solucionarlo?

¡Gracias!
Este comentario fue minimizado por el moderador en el sitio
hola me sale el error "Error de compilación: Nombre ambiguo detectado: xDic
Este comentario fue minimizado por el moderador en el sitio
hola me sale el error "Error de compilación: Nombre ambiguo detectado: xDic
Este comentario fue minimizado por el moderador en el sitio
Hola, soy nuevo en el uso de VBA e intenté usar este código en mi hoja de cálculo, pero el formato de texto en la pestaña Rec2 no aparece en la pestaña Rec cuando se usa la búsqueda. Cualquier ayuda sería muy apreciada. gracias pat
Este comentario fue minimizado por el moderador en el sitio
aqui esta el archivo y la foto
Este comentario fue minimizado por el moderador en el sitio
Recibo el mismo error de nombre ambiguo. ¿Alguien ha logrado resolverlo?
Este comentario fue minimizado por el moderador en el sitio
Recibo el mismo error de nombre ambiguo. ¿Alguien ha logrado resolverlo?
No hay comentarios publicados aquí todavía
Ver más
Deje sus comentarios
Publicar como invitado
×
Califica esta publicación:
0   Personajes
Ubicaciones sugeridas

Seguinos

Copyright © 2009 - www.extendoffice.com. | Reservados todos los derechos. Energizado por ExtendOffice, | Mapa del Sitio
Microsoft y el logotipo de Office son marcas comerciales o marcas comerciales registradas de Microsoft Corporation en los Estados Unidos y / o en otros países.
Protegido por Sectigo SSL