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

Comment copier la mise en forme source de la cellule de recherche lors de l'utilisation de Vlookup dans Excel?

Dans les articles précédents, nous avons parlé de conserver la couleur d'arrière-plan lorsque vlookup prend des valeurs dans Excel. Ici, dans cet article, nous allons présenter une méthode de copie de toute la mise en forme de cellule de la cellule résultante lors de Vlookup dans Excel. Veuillez faire comme suit.

Copier le formatage de la source lors de l'utilisation de Vlookup dans Excel avec une fonction définie par l'utilisateur


Copier le formatage de la source lors de l'utilisation de Vlookup dans Excel avec une fonction définie par l'utilisateur

Supposons que vous ayez un tableau comme ci-dessous. Vous devez maintenant vérifier si une valeur spécifiée (dans la colonne E) est dans la colonne A et renvoyer la valeur correspondante avec le formatage dans la colonne C. Veuillez procéder comme suit pour y parvenir.

1. Dans la feuille de calcul contient la valeur que vous souhaitez rechercher, cliquez avec le bouton droit sur l'onglet de la feuille et sélectionnez Voir le code dans le menu contextuel. Voir la capture d'écran:

2. Dans l'ouverture Microsoft Visual Basic pour applications , veuillez copier ci-dessous le code VBA dans la fenêtre Code.

Code VBA 1: Vlookup et valeur de retour avec mise en forme

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. Puis clique insérer > Module, et copiez le code VBA 2 ci-dessous dans la fenêtre Module.

Code VBA 2: Vlookup et valeur de retour avec mise en forme

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. Cliquez Outils > Références. Puis vérifiez le Exécution de scripts Microsoft boîte dans la Références - VBAProject boite de dialogue. Voir la capture d'écran:

5. appuie sur le autre + Q touches pour quitter Microsoft Visual Basic pour applications fenêtre.

6. Sélectionnez une cellule vide à côté de la valeur de recherche, puis entrez la formule =LookupKeepFormat(E2,$A$1:$C$8,3) into the Barre de formule, puis appuyez sur Entrer clé.

Notez les: Dans la formule, E2 contient la valeur que vous rechercherez, 1 $ AU: 8 $ C $ est la plage de table et le nombre 3 signifie que la valeur correspondante que vous retournerez se situe dans la troisième colonne du tableau. Veuillez les changer selon vos besoins.

7. Continuez à sélectionner la première cellule de résultat, puis faites glisser la poignée de remplissage vers le bas pour obtenir tous les résultats avec leur mise en forme comme illustré ci-dessous.


Articles Liés:


Les meilleurs outils de productivité de bureau

Kutools for Excel Résout la plupart de vos problèmes et augmente votre productivité de 80 %

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtréeplus
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammesplus
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellulesplus
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDFplus
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2021 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Commentaires (42)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
cela me donne une erreur de compilation, une erreur de syntaxe

s'il vous plaît aider
Ce commentaire a été minimisé par le modérateur sur le site
Good Day,
Le code a été mis à jour dans l'artcle. Merci pour votre commentaire.
Ce commentaire a été minimisé par le modérateur sur le site
J'ai aussi eu l'erreur du compilateur.
Il est corrigé si vous modifiez la variable suivante avec le "". Non ';' au milieu.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour,
Désolé pour l'erreur, le code a été mis à jour dans l'article.
L'erreur " " doit être entre deux guillemets " ". Merci pour votre commentaire.
Ce commentaire a été minimisé par le modérateur sur le site
J'ai eu la même erreur.

Vous devrez changer le " " pour réel "', sans ';' comme indiqué ci-dessous
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour,
Désolé pour l'erreur, le code a été mis à jour dans l'article. Merci pour le partage.
Ce commentaire a été minimisé par le modérateur sur le site
C'est super, merci ! Le seul problème est que je trouve que cela fonctionne bien si je recherche dans la même feuille, mais je n'arrive pas à le faire fonctionner lorsque j'essaie de faire une recherche dans une feuille séparée vers les données source. Je vais continuer à essayer
Ce commentaire a été minimisé par le modérateur sur le site
Julia, corrigez ces lignes :
dans Function LookupKeepFormat :
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name

dans Sub Worksheet_Change :
Feuilles(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy
Ce commentaire a été minimisé par le modérateur sur le site
Salut Hugo,


J'ai le même problème que Julia. Cela ne fonctionne pas sur les autres feuilles. Pourriez-vous aider à écrire du code pour l'ensemble de la fonction et de la sous-feuille de calcul ? Je ne sais pas où remplacer/insérer xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Nam and Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy


merci en retour
Ce commentaire a été minimisé par le modérateur sur le site
Appréciez grandement le suivi Hugo!
Malheureusement, comme Vi, je suis trop novice pour savoir où insérer vos correctifs de code suggérés...

Merci encore, bonne journée :)
Ce commentaire a été minimisé par le modérateur sur le site
Salut


J'ai essayé d'utiliser le code mais j'obtiens l'erreur dans la photo ci-jointe. Toute aide sera grandement appréciée.
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour,
Désolé pour l'erreur, le code a été mis à jour dans l'article. Merci pour votre commentaire.
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour,

Je n'obtiens aucune erreur et la recherche est effectuée, mais comme ma valeur de recherche se trouve sur une autre feuille de calcul (un scénario plus probable), elle ne tire pas la mise en forme. Y a-t-il une modification du code que je peux faire pour cela? (Soyez très précis quant à l'endroit où le changement doit aller car je suis un novice en matière de codage) Merci ! Je suis ravi d'ajouter cette fonctionnalité à l'une de mes feuilles de calcul !!
Ce commentaire a été minimisé par le modérateur sur le site
Salut, un peu de chance sur cette question, comment pouvons-nous faire en sorte que la mise en forme soit recherchée sur toutes les feuilles?
Ce commentaire a été minimisé par le modérateur sur le site
Cherche aussi le tweak.
Ce commentaire a été minimisé par le modérateur sur le site
De plus, si j'ajoute votre formule dans le cadre d'une instruction "If" (voir ci-dessous), elle formate la cellule comme elle le souhaite LOL (ou du moins il semble que ce soit le cas. Une cellule, le texte est devenu ombré et gras avec une bordure supérieure sur la cellule ; une autre cellule, le texte centré)


=IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
Ce commentaire a été minimisé par le modérateur sur le site
J'ai essayé celui-ci et celui qui ne tire que le fond de couleur et j'obtiens la même erreur. Erreur de compilation : nom ambigu détecté. Je clique sur OK et il met en surbrillance xDic. Aucune suggestion? Je ne suis pas très au courant de tout cela, alors s'il vous plaît aidez / expliquez :) merci d'avance
Ce commentaire a été minimisé par le modérateur sur le site
Salut Jeni,
N'oubliez pas d'activer l'option Microsoft Script Runtime comme mentionné à l'étape 4.
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour. J'ai créé une feuille de calcul vierge et dupliqué votre exemple dans Excel 2013, mais continue d'obtenir une erreur de compilation : erreur de syntaxe et Dim I As Long est mis en surbrillance. Y a-t-il quelque chose qui me manque? J'aimerais que cela fonctionne. Merci.
Ce commentaire a été minimisé par le modérateur sur le site
Salut Laura,
N'oubliez pas d'activer l'option Microsoft Script Runtime comme mentionné à l'étape 4.
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, j'utilise le code ci-dessus dans Excel 2010 sans aucun problème à ce jour. Cependant, j'ai récemment été mis à niveau vers Office 2016 et maintenant le code plante Excel chaque fois que j'essaie de remplir plus d'une ligne. Malheureusement, cela ne me donne pas d'autre erreur que "Microsoft Excel a cessé de fonctionner". Je me demandais si vous aviez déjà rencontré ce problème et s'il y a quelque chose que je dois faire pour que cela fonctionne en 2016. Merci !
Ce commentaire a été minimisé par le modérateur sur le site
Salut Leigh,
Le code fonctionne bien dans mon Excel 2016. Nous essayons de mettre à jour le code pour résoudre le problème. Merci pour votre commentaire.
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, merci pour le code. Je ne reçois aucun message d'erreur mais la formule ne fonctionne que comme le ferait un vlookup normal. Pourriez-vous m'aider ? Merci pour votre temps.
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour

J'ai exactement le même problème, avez-vous trouvé comment le résoudre ?

Merci !
Ce commentaire a été minimisé par le modérateur sur le site
salut j'ai eu l'erreur "Erreur de compilation : nom ambigu détecté : xDic
Ce commentaire a été minimisé par le modérateur sur le site
salut j'ai eu l'erreur "Erreur de compilation : nom ambigu détecté : xDic
Ce commentaire a été minimisé par le modérateur sur le site
HI, je suis nouveau dans l'utilisation de VBA et j'ai essayé d'utiliser ce code dans ma feuille de calcul, mais la mise en forme du texte sur l'onglet Rec2 ne passe pas à l'onglet Rec lorsque la recherche est utilisée. Toute aide serait grandement appréciée. Merci Pat
Ce commentaire a été minimisé par le modérateur sur le site
Voici le fichier et la photo
Ce commentaire a été minimisé par le modérateur sur le site
J'obtiens la même erreur de nom ambigu - quelqu'un a-t-il réussi à le résoudre ?
Ce commentaire a été minimisé par le modérateur sur le site
J'obtiens la même erreur de nom ambigu - quelqu'un a-t-il réussi à le résoudre ?
Il n'y a pas encore de commentaires postés ici
Télécharger d'autres activités

Nous suivre

Copyright © 2009 - www.extendoffice.com. | Tous les droits sont réservés. Alimenté par ExtendOffice. | | Plan du site
Microsoft et le logo Office sont des marques commerciales ou des marques déposées de Microsoft Corporation aux États-Unis et / ou dans d'autres pays.
Protégé par Sectigo SSL