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

Wie kopiere ich die Quellformatierung der Suchzelle, wenn ich Vlookup in Excel verwende?

In den vorherigen Artikeln haben wir darüber gesprochen, die Hintergrundfarbe bei Vlookup-Werten in Excel beizubehalten. Hier in diesem Artikel stellen wir eine Methode zum Kopieren der gesamten Zellenformatierung der resultierenden Zelle vor, wenn Sie Vlookup in Excel ausführen. Bitte gehen Sie wie folgt vor.

Kopieren Sie die Quellformatierung, wenn Sie Vlookup in Excel mit einer benutzerdefinierten Funktion verwenden


Kopieren Sie die Quellformatierung, wenn Sie Vlookup in Excel mit einer benutzerdefinierten Funktion verwenden

Angenommen, Sie haben eine Tabelle wie im folgenden Screenshot gezeigt. Jetzt müssen Sie überprüfen, ob sich ein bestimmter Wert (in Spalte E) in Spalte A befindet, und den entsprechenden Wert mit der Formatierung in Spalte C zurückgeben. Gehen Sie dazu wie folgt vor.

1. Klicken Sie im Arbeitsblatt mit dem Wert, den Sie anzeigen möchten, mit der rechten Maustaste auf die Registerkarte Blatt und wählen Sie Code anzeigen aus dem Kontextmenü. Siehe Screenshot:

2. In der Öffnung Microsoft Visual Basic für Applikationen Fenster, bitte kopieren Sie den folgenden VBA-Code in das Code-Fenster.

VBA-Code 1: Vlookup und Rückgabewert mit Formatierung

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. Dann klick Insert > Modul, und kopieren Sie den folgenden VBA-Code 2 in das Modulfenster.

VBA-Code 2: Vlookup und Rückgabewert mit Formatierung

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 Klicken Tools > Referenzen. Dann überprüfen Sie die Microsoft Script-Laufzeit Box in der Referenzen - VBAProject Dialogbox. Siehe Screenshot:

5. Drücken Sie die Taste Andere + Q Tasten zum Verlassen des Microsoft Visual Basic für Applikationen Fenster.

6. Wählen Sie eine leere Zelle neben dem Nachschlagewert aus und geben Sie dann die Formel ein =LookupKeepFormat(E2,$A$1:$C$8,3) in die Formel-Bar, und drücken Sie dann die Taste Weiter Key.

Hinweis: In der Formel E2 enthält den Wert, den Sie suchen, $ A $ 1: $ C $ 8 ist der Tabellenbereich und die Nummer 3 bedeutet, dass der entsprechende Wert, den Sie zurückgeben, sich in der dritten Spalte der Tabelle befindet. Bitte ändern Sie sie nach Bedarf.

7. Wählen Sie weiterhin die erste Ergebniszelle aus und ziehen Sie den Füllgriff nach unten, um alle Ergebnisse zusammen mit ihrer Formatierung zu erhalten, wie im folgenden Screenshot gezeigt.


In Verbindung stehende Artikel:


Die besten Tools für die Office-Produktivität

Kutools for Excel Löst die meisten Ihrer Probleme und steigert Ihre Produktivität um 80 %

  • Wiederverwendung: Schnell einfügen komplexe Formeln, Diagramme und alles, was du vorher benutzt hast; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
  • Super Formelriegel (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
  • Zellen / Zeilen / Spalten zusammenführen ohne Daten zu verlieren; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen / Spalten... doppelte Zellen verhindern; Bereiche vergleichen...
  • Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
  • Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
  • Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
  • Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
  • Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
  • Mehr als 300 leistungsstarke Funktionen. Unterstützt Office / Excel 2007-2021 und 365. Unterstützt alle Sprachen. Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation. 30-tägige kostenlose Testversion mit allen Funktionen. 60 Tage Geld-zurück-Garantie.
kte tab 201905

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
officetab unten
Kommentare (43)
Noch keine Bewertungen. Bewerten Sie als Erster!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
es gibt mir Kompilierungsfehler, Syntaxfehler

bitte helfen
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Guten Tag,
Der Code wurde im Artikel aktualisiert. Danke für deinen Kommentar.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich habe auch den Compiler-Fehler.
Es wird korrigiert, wenn Sie die folgende Variable mit dem tatsächlichen "" ändern. Nein ';' mitten drin.
LookupKeepFormat = " "
xDic.Anwendung.Anrufer.Adresse hinzufügen, " "
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,
Entschuldigung für den Fehler, der Code wurde im Artikel aktualisiert.
Der Fehler „ “ sollte aus zwei Anführungszeichen „ “ bestehen. Danke für deinen Kommentar.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich habe den gleichen Fehler bekommen.

Sie müssen das " " durch das tatsächliche "' ohne ';' ändern. wie unten angegeben
LookupKeepFormat = " "
xDic.Anwendung.Anrufer.Adresse hinzufügen, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,
Entschuldigung für den Fehler, der Code wurde im Artikel aktualisiert. Ich danke Ihnen für das Teilen.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das ist großartig, danke! Das einzige Problem ist, dass ich finde, dass es gut funktioniert, wenn ich im selben Blatt nachschaue, es aber nicht zum Laufen bringt, wenn ich versuche, in einem separaten Blatt nach den Quelldaten zu suchen. Werde es weiter versuchen
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Julia, korrigiere diese Zeilen:
in Funktion LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name

in Sub Worksheet_Change:
Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Hugo,


Ich habe das gleiche Problem wie Julia. Auf anderen Blättern funktioniert es nicht. Könnten Sie beim Schreiben von Code für die gesamte Funktion und das Unterarbeitsblatt helfen? Ich bin mir nicht sicher, wo ich xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" ersetzen/einfügen soll. & LookupRng.Parent.Nam and Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy


danke im gegenzug
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Schätzen Sie das Follow-up Hugo sehr!
Leider bin ich wie Vi zu sehr ein Neuling, um herauszufinden, wo Sie Ihre vorgeschlagenen Code-Korrekturen einfügen können ...

Nochmals vielen Dank, ich wünsche Ihnen einen schönen Tag :)
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo


Ich habe versucht, den Code zu verwenden, erhalte jedoch den Fehler im angehängten Bild. Jede Unterstützung wird sehr geschätzt.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,
Entschuldigung für den Fehler, der Code wurde im Artikel aktualisiert. Danke für deinen Kommentar.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,

Ich bekomme keine Fehler und es führt die Suche durch, aber da sich mein Suchwert auf einem anderen Arbeitsblatt befindet (ein wahrscheinlicheres Szenario), wird die Formatierung nicht abgerufen. Gibt es eine Änderung am Code, die ich dafür vornehmen kann? (Seien Sie sehr genau, wohin die Änderung gehen muss, da ich ein Codierungsneuling bin) Vielen Dank! Ich freue mich darauf, diese Funktion zu einer meiner Tabellenkalkulationen hinzuzufügen!!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, viel Glück bei dieser Frage, wie können wir die Formatierung dazu bringen, blattübergreifend nachzuschlagen?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Suche auch den Tweak.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wenn ich Ihre Formel als Teil einer "If" -Anweisung hinzufüge (siehe unten), formatiert sie die Zelle so, wie sie LOL will (oder zumindest scheint es so. In einer Zelle wurde der Text schattiert und fett mit einem oberen Rand). die Zelle; eine andere Zelle, der Text zentriert)


=IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich habe dieses und das ausprobiert, das nur den Farbhintergrund zieht, und erhalte den gleichen Fehler. Kompilierfehler: Mehrdeutiger Name erkannt. Ich klicke auf OK und es hebt xDic hervor. Irgendwelche Vorschläge? Ich bin mit all dem nicht sehr vertraut, also bitte um Hilfe/Erklärung :) Danke im Voraus
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Jeni,
Vergessen Sie nicht, die Option Microsoft Script Runtime zu aktivieren, wie in Schritt 4 erwähnt.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo. Ich habe eine leere Tabelle erstellt und Ihr Beispiel in Excel 2013 dupliziert, erhalte aber weiterhin einen Kompilierungsfehler: Syntaxfehler und Dim I As Long ist hervorgehoben. Gibt es etwas, das ich vermisse? Ich würde das gerne zum Laufen bringen. Vielen Dank.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Laura,
Vergessen Sie nicht, die Option Microsoft Script Runtime zu aktivieren, wie in Schritt 4 erwähnt.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, ich habe den obigen Code in Excel 2010 bisher ohne Probleme verwendet. Ich wurde jedoch kürzlich auf Office 2016 aktualisiert und jetzt stürzt der Code Excel jedes Mal ab, wenn ich versuche, mehr als eine Zeile auszufüllen. Leider gibt es mir keinen anderen Fehler als "Microsoft Excel funktioniert nicht mehr". Ich habe mich gefragt, ob Sie schon früher auf dieses Problem gestoßen sind und ob ich etwas tun muss, damit es 2016 funktioniert. Danke!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Leigh,
Der Code funktioniert gut in meinem Excel 2016. Wir versuchen, den Code zu aktualisieren, um das Problem zu lösen. Danke für deinen Kommentar.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, danke für den Code. Ich erhalte keine Fehlermeldung, aber die Formel funktioniert nur wie ein normaler Vlookup. Könnten Sie bitte helfen? Vielen Dank für Ihre Zeit.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo

Ich habe genau das gleiche Problem, hast du herausgefunden, wie man es löst?

Vielen Dank!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, ich habe den Fehler "Kompilierungsfehler: Mehrdeutiger Name erkannt: xDic
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, ich habe den Fehler "Kompilierungsfehler: Mehrdeutiger Name erkannt: xDic
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, ich bin neu in der Verwendung von VBA und habe versucht, diesen Code in meiner Tabelle zu verwenden, aber die Textformatierung auf der Registerkarte „Rec2“ wird nicht auf die Registerkarte „Rec“ übertragen, wenn die Suche verwendet wird. Jede Hilfe wäre sehr willkommen. Danke Pat
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hier ist die Datei und das Bild
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich erhalte den gleichen mehrdeutigen Namensfehler - hat es jemand geschafft, ihn zu lösen?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich erhalte den gleichen mehrdeutigen Namensfehler - hat es jemand geschafft, ihn zu lösen?
Es sind noch keine Kommentare vorhanden
MEHR LADEN

Folgen Sie uns

Copyright © 2009 - www.extendoffice.com. | Alle Rechte vorbehalten. Unterstützt von ExtendOffice. | Sitemap
Microsoft und das Office-Logo sind Marken oder eingetragene Marken der Microsoft Corporation in den USA und / oder anderen Ländern.
Geschützt durch Sectigo SSL