Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

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 les valeurs de vlookup dans Excel. Ici, dans cet article, nous allons introduire une méthode de copie de toute la mise en forme des cellules de la cellule résultante lorsque vous effectuez Vlookup dans Excel. S'il vous plaît faire comme suit.

Copier la mise en forme de la source lors de l'utilisation de Vlookup dans Excel avec une fonction définie par l'utilisateur


Combiner plusieurs feuilles de calcul / classeurs dans une seule feuille de calcul / classeur:

Combiner plusieurs feuilles de calcul ou classeurs dans une seule feuille de calcul ou classeur peut être une tâche énorme dans votre travail quotidien. Mais, si vous avez Kutools for Excel, son utilité puissante - Combiner peut vous aider à combiner rapidement plusieurs feuilles de calcul, classeurs dans une feuille de calcul ou un classeur

Kutools for Excel: avec plus que 200 compléments Excel pratiques, libre d'essayer sans limitation dans les jours 60. Téléchargez et essai gratuit maintenant!


Copier la mise en forme de la source lors de l'utilisation de Vlookup dans Excel avec une fonction définie par l'utilisateur


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

1. Dans la feuille de calcul contient la valeur que vous souhaitez vlookup, cliquez avec le bouton droit sur l'onglet de 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 fenêtre, veuillez copier ci-dessous le code VBA dans la fenêtre Code.

Code VBA 1: Vlookup et valeur de retour avec formatage

Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180706
    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
                Range(xDic.Items(I)).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 > Moduleet copiez le code VBA 2 ci-dessous dans la fenêtre Module.

Code VBA 2: Vlookup et valeur de retour avec formatage

Public xDic As New Dictionary
'Update by Extendoffice 20180706
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
    End If
    Application.ScreenUpdating = True
End Function

4. Cliquez Outils > Les références. Ensuite, vérifiez le Microsoft Script Runtime 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 le Microsoft Visual Basic pour applications fenêtre.

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

Note: Dans la formule, E2 contient la valeur que vous allez rechercher, $ A $ 1: $ C $ 8 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. S'il vous plaît, changez-les 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 ainsi que leur formatage comme illustré ci-dessous.


Articles Liés:



Outils de productivité recommandés

Office Tab

étoile d&#39;or1 Apportez des onglets pratiques à Excel et à d'autres logiciels Office, tout comme Chrome, Firefox et Internet Explorer.

Kutools for Excel

étoile d&#39;or1 Incroyable! Augmentez votre productivité dans les minutes 5. Ne nécessite pas de compétences particulières, économisez deux heures par jour!

étoile d&#39;or1 300 Nouvelles fonctionnalités pour Excel, rendent Excel facile et puissant:

  • Fusionner des cellules / lignes / colonnes sans perdre de données.
  • Combiner et consolider plusieurs feuilles et classeurs.
  • Comparez les gammes, copiez plusieurs gammes, convertissez le texte en date, l'unité et la conversion de devise.
  • Compter par couleurs, sous-totaux de recherche, tri avancé et super filtre,
  • Plus Sélectionner / Insérer / Supprimer / Texte / Format / Lier / Commenter / Classeurs / Feuilles de calcul Outils ...

Capture d&#39;écran de Kutools pour Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Heather M · 7 months ago
    Also, if I add your formula as part of an "If" statement (see below), it formats the cell however it wants LOL (or at least it seems so. One cell, the text went shadowed and bold with a top border on the cell; another cell, the text centered)


    =IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
  • To post as a guest, your comment is unpublished.
    Heather M · 7 months ago
    Hi,

    I get no errors and it does the lookup, but because my lookup value is on another worksheet (a more likely scenario), it doesn't pull the formatting. Is there a tweak to the code that I can make for that? (Be very specific as to where the change needs to go as I'm a coding novice) Thank you! I'm excited to add this feature to one of my spreadsheets!!
    • To post as a guest, your comment is unpublished.
      Chirag · 6 months ago
      Hi, any luck on this question, how can we get the formatting to be looked up across sheets?
  • To post as a guest, your comment is unpublished.
    Nivian Govender · 10 months ago
    Hi There


    I have tried to use the code however I am getting the error in the attached pic. Any assisting will be greatly appreciated.
    • To post as a guest, your comment is unpublished.
      crystal · 9 months ago
      Hi,
      Sorry for the mistake, the code has been updated in the article. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Julia · 10 months ago
    Greatly appreciate the follow-up Hugo!
    Unfortunately like Vi, I am too much of a novice to work out where to insert your suggested code fixes...

    Thanks again, have a great day :)
  • To post as a guest, your comment is unpublished.
    Vi · 10 months ago
    Hey Hugo,


    I have the same problem as Julia. It doesn't work on other sheets. Could you help write code for the whole function and sub worksheet? I am not sure where to replace/insert 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


    thanks in return