Ana içeriğe atla

Excel'de Vlookup kullanırken arama hücresinin kaynak biçimlendirmesi nasıl kopyalanır?

Önceki yazılarda, Excel'de vlookup değerleri olduğunda arka plan renginin korunmasından bahsetmiştik. İşte bu makalede, Excel'de Vlookup yaparken ortaya çıkan hücrenin tüm hücre biçimlendirmesini kopyalamanın bir yöntemini tanıtacağız. Lütfen aşağıdaki işlemleri yapın.

Excel'de Kullanıcı tanımlı bir işlevle Vlookup kullanırken kaynak biçimlendirmesini kopyalayın

Excel'de Kullanıcı tanımlı bir işlevle Vlookup kullanırken kaynak biçimlendirmesini kopyalayın

Aşağıda gösterilen ekran görüntüsü gibi bir tablonuz olduğunu varsayarsak. Şimdi, belirli bir değerin (E sütununda) A sütununda olup olmadığını kontrol etmeniz ve C sütununda biçimlendirmeyle karşılık gelen değeri döndürmeniz gerekir. Bunu elde etmek için lütfen aşağıdaki işlemleri yapın.

1. Çalışma sayfasında, vlookup etmek istediğiniz değeri içerir, sayfa sekmesine sağ tıklayın ve Kodu Görüntüle bağlam menüsünden. Ekran görüntüsüne bakın:

2. Açılışta Uygulamalar için Microsoft Visual Basic penceresi, lütfen aşağıdaki VBA kodunu Kod penceresine kopyalayın.

VBA kodu 1: Vlookup ve biçimlendirmeyle dönüş değeri

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. Sonra tıklayın Ekle > modülve aşağıdaki VBA kodu 2'yi Modül penceresine kopyalayın.

VBA kodu 2: Vlookup ve biçimlendirmeyle dönüş değeri

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. tık Tools > Referanslar. Sonra kontrol edin Microsoft Komut Dosyası Çalışma Zamanı kutusu Referanslar - VBAProject iletişim kutusu. Ekran görüntüsüne bakın:

5. Tuşuna basın. Ara Toplam + Q çıkış tuşları Uygulamalar için Microsoft Visual Basic pencere.

6. Arama değerinin yanında boş bir hücre seçin ve ardından formülü girin =LookupKeepFormat(E2,$A$1:$C$8,3) içine Formül çubuğutuşuna basın ve ardından tuşuna basın. Keşfet tuşuna basın.

not: Formülde, E2 arayacağınız değeri içerir, $ A $ 1: $ C $ 8 tablo aralığı ve numarasıdır 3 döndürdüğünüz karşılık gelen değerin tablonun üçüncü sütununda yer aldığı anlamına gelir. Lütfen ihtiyaç duyduğunuzda değiştirin.

7. İlk sonuç hücresini seçmeye devam edin ve ardından aşağıdaki ekran görüntüsünde gösterildiği gibi biçimlendirmeleriyle birlikte tüm sonuçları almak için Dolgu Tutamaçını aşağı sürükleyin.

İlgili yazılar:

En İyi Ofis Üretkenlik Araçları

🤖 Kutools AI Yardımcısı: Aşağıdakilere dayalı olarak veri analizinde devrim yaratın: Akıllı Yürütme   |  Kodunu oluşturun  |  Özel Formüller Oluşturun  |  Verileri Analiz Edin ve Grafikler Oluşturun  |  Kutools İşlevlerini Çağır...
Popüler Özellikler: Yinelenenleri Bul, Vurgula veya Tanımla   |  Boş Satırları Sil   |  Veri Kaybı Olmadan Sütunları veya Hücreleri Birleştirin   |   Formülsüz Tur ...
Süper Arama: Çoklu Ölçütlü VLookup  |   Çoklu Değer VLookup  |   Birden Çok Sayfada VLookup   |   Bulanık Arama ....
Gelişmiş Açılır Liste: Hızla Açılır Liste Oluşturun   |  Bağımlı Açılır Liste   |  Çoklu Seçim Açılır Liste ....
Sütun Yöneticisi: Belirli Sayıda Sütun Ekleme  |  Sütunları Taşı  |  Gizli Sütunların Görünürlük Durumunu Değiştir  |  Aralıkları ve Sütunları Karşılaştırın ...
Öne Çıkan Özellikler: Izgara Odağı   |  Tasarım görünümü   |   Büyük Formül Çubuğu   |  Çalışma Kitabı ve Sayfa Yöneticisi   |  Kaynak Kütüphanesi (Otomatik metin)   |  Tarih Seçici   |  Çalışma Sayfalarını Birleştirin   |  Hücreleri Şifrele/Şifresini Çöz   |  E-postaları Listeye Göre Gönder   |  Süper Filtre   |   Özel Filtre (kalın/italik/üstü çizili filtre...) ...
En İyi 15 Araç Seti:  12 Metin Tools (Metin ekle, Karakterleri Kaldır, ...)   |   50+ Grafik Türleri (Gantt şeması, ...)   |   40+ Pratik Formüller (Yaşı doğum gününe göre hesapla, ...)   |   19 sokma Tools (QR Kodunu Girin, Yoldan Resim Ekle, ...)   |   12 Dönüştürme Tools (Sayılardan Kelimelere, Para Birimi Dönüştürme, ...)   |   7 Birleştir ve Böl Tools (Gelişmiş Kombine Satırları, Bölünmüş hücreler, ...)   |   ... ve dahası

Kutools for Excel ile Excel Becerilerinizi Güçlendirin ve Daha Önce Hiç Olmadığı Gibi Verimliliği Deneyimleyin. Kutools for Excel, Üretkenliği Artırmak ve Zamandan Tasarruf Etmek için 300'den Fazla Gelişmiş Özellik Sunar.  En Çok İhtiyacınız Olan Özelliği Almak İçin Buraya Tıklayın...

Açıklama

Office Tab, Office'e Sekmeli Arayüz Getirir ve İşinizi Çok Daha Kolay Hale Getirir

  • Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
  • Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
  • Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!
Comments (44)
No ratings yet. Be the first to rate!
Dorina
Dorina
This comment was minimized by the moderator on the site
It seems to work but excel freezes from it and goes unresponsive. HELP!!!!
Reply
0
0
Kaila
Kaila
This comment was minimized by the moderator on the site
So - this macro works, but every time I use it my spreadsheet stops responding for roughly 3 minutes (even for one single line of data). Any tips?
Reply
0
0
slb
slb
This comment was minimized by the moderator on the site
Is there a way to use this on the same sheet with two different lookups. Ie. Lookup Column M in array A:B, return column B with formatting. Then Lookup in Column N in array C:D and return column D with formatting?
Ive got the first set working perfectly, and the second set wont work at all. No error, just most of the rows are blank
Reply
0
0
kirtiraj
kirtiraj
This comment was minimized by the moderator on the site
This code only works when data is in same sheet.
Reply
0
0
crystal
crystal     kirtiraj
This comment was minimized by the moderator on the site
Hi kirtiraj,To lookup values across worksheets and keep the formatting, you need to place the VBA code 1 in the code window of the result worksheet, and place the VBA code 2 in the Module code window.
Reply
Report
0
0
John
John
This comment was minimized by the moderator on the site
I get a compile error: "Expected: end of statement", with the word "New" highlighted in: "Public xDic As New Dictionary".
I'm not a developer, just trying to solve a problem in a long set of sheets. So thank you for the help.
Reply
0
0
M B
M B
This comment was minimized by the moderator on the site
HeyThe code does not work in Microsoft Excel 2019 (16.0.13929.20360) 64-bit Can provide details if asked...
Reply
0
0
ed
ed     M B
This comment was minimized by the moderator on the site
Please provide details
Reply
0
0
ABCD
ABCD
This comment was minimized by the moderator on the site
How to make this work if the value we are trying to look up sits in a different worksheet?
Reply
0
0
crystal
crystal     ABCD
This comment was minimized by the moderator on the site
Hi,
To lookup values across worksheets and keep the formatting, you need to place the VBA code 1 in the code window of the result worksheet, and place the VBA code 2 in the Module code window.
Reply
Report
1
0
FrenchToastKB
FrenchToastKB     crystal
This comment was minimized by the moderator on the site
That's Bad***!
Thanks for the coding and the tip on how to make the formula work across separate worksheets, friend.
However the coding should modified for the Subworksheet_Change for the one below:
Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20230328
Dim I As Long
Dim xKeys As Long
Dim xDicStr As String
On Error Resume Next
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.EnableEvents = 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
Application.EnableEvents = True
End Sub


If you combine the coding I wrote below for the Result worksheet and the coding provided here in the Module, it will work when using separate worksheets.
You'll thank me later!
Cheers
Reply
Report
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
So, I got this to work. However, how I'm using it is I have the lookupkeepformat formula already entered in multiple rows. I then enter a letter (A-J) in column A and this letter tells the lookup formula which data I want. After it pulls the data, the cursor ends up in the cell where it finished entering the lookup data. How can I have the cursor return to column A?
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
I'm getting an error
Reply
0
0
Rah2153
Rah2153
This comment was minimized by the moderator on the site
I am adding these modules to my PERSONAL.XLSB file. I have Outlook 2016. And when I use this user-defined function, my excel doesn't crash or give an error. But it does not pull in the format of the source cell. It only pulls in the value. Since this function is located in the PERSONAL.XLSB file, my formula looks like this;=PERSONAL.xlsb!LookupKeepFormat(E2,$A$1:$C$8,3)
I had copied the code into 2 modules as directed, but this just doesn't work. Any ideas why?
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations