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

Hoe de bronopmaak van de opzoekcel te kopiëren bij gebruik van Vlookup in Excel?

In de vorige artikelen hebben we het gehad over het behouden van de achtergrondkleur bij vlookup-waarden in Excel. Hier in dit artikel gaan we een methode introduceren om alle celopmaak van de resulterende cel te kopiëren bij het uitvoeren van Vlookup in Excel. Ga als volgt te werk.

Kopieer de bronopmaak bij gebruik van Vlookup in Excel met een door de gebruiker gedefinieerde functie


Kopieer de bronopmaak bij gebruik van Vlookup in Excel met een door de gebruiker gedefinieerde functie

Stel dat u een tabel heeft zoals hieronder afgebeeld. Nu moet u controleren of een gespecificeerde waarde (in kolom E) in kolom A staat en de corresponderende waarde retourneren met opmaak in kolom C. Ga als volgt te werk om dit te bereiken.

1. Bevat in het werkblad de waarde die u wilt opvullen, klik met de rechtermuisknop op de bladtab en selecteer Bekijk code vanuit het contextmenu. Zie screenshot:

2. In de opening Microsoft Visual Basic voor toepassingen -venster, kopieer de onderstaande VBA-code naar het codevenster.

VBA-code 1: Vlookup en retourwaarde met opmaak

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. Dan klikken Invoegen > Moduleen kopieer de onderstaande VBA-code 2 naar het modulevenster.

VBA-code 2: Vlookup en retourwaarde met opmaak

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. klikken Toolbox > Referenties. Controleer dan het Microsoft Script-runtime box aan de Referenties - VBAProject dialoog venster. Zie screenshot:

5. druk de anders + Q toetsen om het Microsoft Visual Basic voor toepassingen venster.

6. Selecteer een lege cel naast de opzoekwaarde en voer de formule in =LookupKeepFormat(E2,$A$1:$C$8,3) in de Formule balken druk vervolgens op Enter sleutel.

Opmerking:: In de formule, E2 bevat de waarde die u zoekt, $ A $ 1: $ C $ 8 is het tafelbereik en het nummer 3 betekent dat de overeenkomstige waarde die u retourneert, zich in de derde kolom van de tabel bevindt. Wijzig ze indien nodig.

7. Blijf de eerste resultaatcel selecteren en sleep vervolgens de vulhendel naar beneden om alle resultaten samen met hun opmaak te zien, zoals hieronder getoond.


Gerelateerde artikelen:


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2021 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Volledige functies Gratis proefperiode van 30 dagen. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
officetab onderkant
Heb je vragen? Stel ze hier. (42)
Nog geen beoordelingen. Beoordeel als eerste!
Deze opmerking is gemaakt door de moderator op de site
het geeft me een compileerfout, syntaxisfout

please help
Deze opmerking is gemaakt door de moderator op de site
Good Day,
De code is bijgewerkt in het artikel. Bedankt voor je reactie.
Deze opmerking is gemaakt door de moderator op de site
Ik kreeg ook de compilerfout.
Het wordt gecorrigeerd als u de volgende variabele wijzigt in werkelijke "". Nee ';' middenin.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Deze opmerking is gemaakt door de moderator op de site
Hoi,
Sorry voor de fout, de code is bijgewerkt in het artikel.
De fout " " moet tussen twee aanhalingstekens " " staan. Bedankt voor je reactie.
Deze opmerking is gemaakt door de moderator op de site
Ik kreeg dezelfde fout.

U zult de " " moeten vervangen door werkelijke "', zonder ';' zoals hieronder aangegeven
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Deze opmerking is gemaakt door de moderator op de site
Hoi,
Sorry voor de fout, de code is bijgewerkt in het artikel. Dank je wel voor het delen.
Deze opmerking is gemaakt door de moderator op de site
Dit is geweldig, bedankt! Het enige probleem is dat ik vind dat het prima werkt als ik in hetzelfde blad opzoek, maar het niet aan de praat krijg als ik probeer een opzoeking te doen in een apart blad naar de brongegevens. Zal blijven proberen
Deze opmerking is gemaakt door de moderator op de site
Julia, corrigeer deze regels:
in Functie LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Naam

in Sub Worksheet_Change:
Bladen(Split(xDic.Items(I), "|")(1)).Bereik(Split(xDic.Items(I), "|")(0)).Kopiëren
Deze opmerking is gemaakt door de moderator op de site
Hé Hugo,


Ik heb hetzelfde probleem als Julia. Op andere bladen werkt het niet. Kun je helpen code te schrijven voor de hele functie en het subwerkblad? Ik weet niet zeker waar ik xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" moet vervangen/invoegen & LookupRng.Parent.Nam en Spreadsheets(Split(xDic.Items(I), "|")(1)).Bereik(Split(xDic.Items(I), "|")(0)).Kopiëren


bedankt in ruil daarvoor
Deze opmerking is gemaakt door de moderator op de site
Waardeer ten zeerste de follow-up Hugo!
Helaas ben ik, net als Vi, een te grote beginner om uit te zoeken waar je de voorgestelde codefixes moet invoegen...

Nogmaals bedankt, fijne dag verder :)
Deze opmerking is gemaakt door de moderator op de site
Hallo daar


Ik heb geprobeerd de code te gebruiken, maar ik krijg de fout in de bijgevoegde foto. Elke hulp wordt zeer op prijs gesteld.
Deze opmerking is gemaakt door de moderator op de site
Hoi,
Sorry voor de fout, de code is bijgewerkt in het artikel. Bedankt voor je reactie.
Deze opmerking is gemaakt door de moderator op de site
Hoi,

Ik krijg geen fouten en het doet de opzoeking, maar omdat mijn opzoekwaarde zich op een ander werkblad bevindt (een waarschijnlijker scenario), wordt de opmaak niet opgehaald. Is er een tweak aan de code die ik daarvoor kan maken? (Wees heel specifiek over waar de wijziging naartoe moet, aangezien ik een beginneling ben in coderen) Bedankt! Ik ben verheugd om deze functie toe te voegen aan een van mijn spreadsheets!!
Deze opmerking is gemaakt door de moderator op de site
Hallo, veel geluk met deze vraag, hoe kunnen we ervoor zorgen dat de opmaak op verschillende bladen wordt opgezocht?
Deze opmerking is gemaakt door de moderator op de site
Ben ook op zoek naar de tweak.
Deze opmerking is gemaakt door de moderator op de site
En als ik je formule toevoeg als onderdeel van een "Als"-instructie (zie hieronder), wordt de cel opgemaakt zoals hij wil LOL (of dat lijkt tenminste zo. Bij één cel werd de tekst geschaduwd en vetgedrukt met een bovenrand op de cel; een andere cel, de tekst gecentreerd)


=IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
Deze opmerking is gemaakt door de moderator op de site
Ik heb deze geprobeerd en degene die alleen de gekleurde achtergrond trekt en krijg dezelfde foutmelding. Compileerfout: dubbelzinnige naam gedetecteerd. Ik klik op OK en het markeert xDic. Suggesties? Ik ben niet super bekend met dit alles, dus help / leg uit :) alvast bedankt
Deze opmerking is gemaakt door de moderator op de site
Hallo Jeni,
Vergeet niet de Microsoft Script Runtime-optie in te schakelen zoals vermeld in stap 4.
Deze opmerking is gemaakt door de moderator op de site
Hallo. Ik heb een lege spreadsheet gemaakt en uw voorbeeld gedupliceerd in Excel 2013, maar ik krijg steeds een compilatiefout: syntaxisfout en Dim I As Long is gemarkeerd. Is er iets dat ik mis? Ik zou dit graag werkend krijgen. Dank je.
Deze opmerking is gemaakt door de moderator op de site
Hallo Laura,
Vergeet niet de Microsoft Script Runtime-optie in te schakelen zoals vermeld in stap 4.
Deze opmerking is gemaakt door de moderator op de site
Hallo, ik heb de bovenstaande code tot nu toe zonder problemen in Excel 2010 gebruikt. Ik heb echter onlangs een upgrade naar Office 2016 ondergaan en nu crasht de code Excel telkens wanneer ik meer dan één rij probeer in te vullen. Helaas geeft het me geen andere foutmelding dan "Microsoft Excel werkt niet meer". Ik vroeg me af of je dit probleem eerder bent tegengekomen en of ik iets moet doen om het in 2016 te laten werken. Bedankt!
Deze opmerking is gemaakt door de moderator op de site
Hallo Leigh,
De code werkt goed in mijn Excel 2016. We proberen de code te upgraden om het probleem op te lossen. Bedankt voor je reactie.
Deze opmerking is gemaakt door de moderator op de site
Hallo, bedankt voor de code. Ik krijg geen foutmelding, maar de formule werkt alleen zoals een normale vlookup zou doen. Kunt u alstublieft helpen? Bedankt voor uw tijd.
Deze opmerking is gemaakt door de moderator op de site
Hallo

Ik heb precies hetzelfde probleem, ben je er al achter hoe dit op te lossen?

Bedankt!
Deze opmerking is gemaakt door de moderator op de site
hallo ik kreeg de fout "compileren Fout: onduidelijke naam gedetecteerd: xDic
Deze opmerking is gemaakt door de moderator op de site
hallo ik kreeg de fout "compileren Fout: onduidelijke naam gedetecteerd: xDic
Deze opmerking is gemaakt door de moderator op de site
Hallo, ik ben nieuw in het gebruik van VBA en heb geprobeerd deze code in mijn spreadsheet te gebruiken, maar de tekstopmaak op het tabblad Rec2 komt niet over op het tabblad Rec wanneer opzoeken wordt gebruikt. Alle hulp wordt zeer op prijs gesteld. Bedankt Pat
Deze opmerking is gemaakt door de moderator op de site
Hier is het bestand en de foto
Deze opmerking is gemaakt door de moderator op de site
Ik krijg dezelfde dubbelzinnige naamfout - is het iemand gelukt om het op te lossen?
Deze opmerking is gemaakt door de moderator op de site
Ik krijg dezelfde dubbelzinnige naamfout - is het iemand gelukt om het op te lossen?
Er zijn nog geen reacties geplaatst
Laad meer
Laat uw commentaar
Posten als gast
×
Beoordeel dit bericht:
0   Personages
Voorgestelde locaties

Volg ons

Copyright © 2009 - www.extendoffice.com. | Alle rechten voorbehouden. Aangedreven door ExtendOffice. | Sitemap
Microsoft en het Office-logo zijn handelsmerken of gedeponeerde handelsmerken van Microsoft Corporation in de Verenigde Staten en / of andere landen.
Beschermd door Sectigo SSL