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

在Excel中使用Vlookup時如何復制查找單元格的源格式?

在先前的文章中,我們討論了在Excel中的vlookup值時保持背景顏色的問題。 在本文的此處,我們將介紹一種在Excel中執行Vlookup時復制生成的單元格的所有單元格格式的方法。 請執行以下操作。

在具有用戶定義功能的Excel中使用Vlookup時復制源格式


在具有用戶定義功能的Excel中使用Vlookup時復制源格式

假設您有一個表格,如下所示。 現在,您需要檢查指定的值(在E列中)是否在A列中,並以C列中的格式返回相應的值。請執行以下操作以實現該目的。

1.在工作表中包含您要vlookup的值,右鍵單擊工作表選項卡,然後選擇 查看代碼 從上下文菜單中。 看截圖:

2.在開幕 Microsoft Visual Basic for Applications 窗口,請將下面的VBA代碼複製到“代碼”窗口中。

VBA代碼1:Vlookup和帶有格式化的返回值

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。 然後點擊 插入 > 模塊,然後將下面的VBA代碼2複製到“模塊”窗口中。

VBA代碼2:Vlookup和帶有格式化的返回值

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。 點擊 工具 > 參考。 然後檢查 Microsoft腳本運行時 盒子裡 參考– VBAProject 對話框。 看截圖:

5。 按 其他 + Q 退出鍵 Microsoft Visual Basic for Applications 窗口。

6.選擇一個與查詢值相鄰的空白單元格,然後輸入公式 =LookupKeepFormat(E2,$A$1:$C$8,3) 配方欄,然後按 Enter 鍵。

備註: 在公式, E2 包含您將要查找的值, $ A $ 1:$ C $ 8 是表格範圍和數字 3 表示您將返回的相應值位於表的第三列中。 請根據需要更改它們。

7.繼續選擇第一個結果單元格,然後向下拖動“填充手柄”以獲取所有結果及其格式,如下面的屏幕快照所示。


相關文章:


最佳辦公效率工具

Kutools for Excel 解決了你的大部分問題,並將你的生產力提高了 80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過 300 項強大的功能. 支持 Office / Excel 2007-2021 和 365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能 30 天免費試用。 60 天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部
按評論排序
留言 (42)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
它給我編譯錯誤,語法錯誤

請大家幫忙
網站主持人對此評論進行了最小化
美好的一天,
代碼已在文章中更新。 感謝您的評論。
網站主持人對此評論進行了最小化
我也得到了編譯器錯誤。
如果您用實際的“”更改以下變量,它會得到糾正。 不 ';' 在中間。
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
網站主持人對此評論進行了最小化
嗨,
對不起,代碼已在文章中更新。
錯誤“”應該是兩個引號“”。 感謝您的評論。
網站主持人對此評論進行了最小化
我遇到了同樣的錯誤。

您必須將“”更改為實際的“',而不使用';' 如下所示
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
網站主持人對此評論進行了最小化
嗨,
對不起,代碼已在文章中更新。 感謝你的分享。
網站主持人對此評論進行了最小化
這太好了,謝謝! 唯一的問題是,如果我在同一張工作表中查找它,我發現它工作正常,但是當我嘗試在單獨的工作表中查找源數據時無法讓它工作。 會繼續努力
網站主持人對此評論進行了最小化
朱莉婭,糾正這行:
在函數 LookupKeepFormat 中:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name

在子 Worksheet_Change 中:
表(拆分(xDic.Items(I),“|”)(1)).Range(拆分(xDic.Items(I),“|”)(0))。複製
網站主持人對此評論進行了最小化
嘿雨果,


我和朱莉婭有同樣的問題。 它不適用於其他工作表。 你能幫忙為整個函數和子工作表編寫代碼嗎? 我不確定在哪裡替換/插入 xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Nam 和 Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy


感謝回報
網站主持人對此評論進行了最小化
非常感謝後續雨果!
不幸的是,像 Vi 一樣,我太新手了,無法弄清楚在哪裡插入建議的代碼修復......

再次感謝,祝你有美好的一天:)
網站主持人對此評論進行了最小化
你好


我曾嘗試使用該代碼,但在附件圖片中出現錯誤。 任何協助將不勝感激。
網站主持人對此評論進行了最小化
嗨,
對不起,代碼已在文章中更新。 感謝您的評論。
網站主持人對此評論進行了最小化
嗨,

我沒有收到任何錯誤,它會進行查找,但是因為我的查找值在另一個工作表上(更可能的情況),所以它不會提取格式。 是否可以對代碼進行調整? (因為我是編碼新手,所以要非常具體地說明更改需要去哪裡)謝謝! 我很高興將此功能添加到我的一個電子表格中!!
網站主持人對此評論進行了最小化
嗨,在這個問題上運氣好,我們如何才能跨工作表查找格式?
網站主持人對此評論進行了最小化
也在尋求調整。
網站主持人對此評論進行了最小化
此外,如果我將您的公式添加為“If”語句的一部分(見下文),它會根據需要設置單元格的格式(或者至少看起來如此。一個單元格,文本變為陰影和粗體,頂部邊框在單元格;另一個單元格,文本居中)


=IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
網站主持人對此評論進行了最小化
我試過這個和只拉彩色背景的那個,我得到了同樣的錯誤。 編譯錯誤:檢測到不明確的名稱。 我單擊“確定”,它會突出顯示 xDic。 有什麼建議麼? 我對所有這些都不是很熟悉,所以請幫助/解釋:)提前謝謝
網站主持人對此評論進行了最小化
嗨,珍妮,
不要忘記啟用步驟 4 中提到的 Microsoft 腳本運行時選項。
網站主持人對此評論進行了最小化
你好。 我創建了一個空白電子表格並在 Excel 2013 中復制了您的示例,但不斷收到編譯錯誤:語法錯誤和 Dim I As Long 突出顯示。 有什麼我想念的嗎? 我很想讓這個工作。 謝謝你。
網站主持人對此評論進行了最小化
嗨勞拉,
不要忘記啟用步驟 4 中提到的 Microsoft 腳本運行時選項。
網站主持人對此評論進行了最小化
您好,我一直在 Excel 2010 中使用上述代碼,迄今為止沒有任何問題。 但是,我最近升級到 Office 2016,現在每次我嘗試填寫多行時,代碼都會導致 Excel 崩潰。 不幸的是,除了“Microsoft Excel 已停止工作”之外,它沒有給我一個錯誤。 我想知道您以前是否遇到過這個問題,以及我是否需要做些什麼才能使其在 2016 年正常工作。謝謝!
網站主持人對此評論進行了最小化
嗨,利,
該代碼在我的 Excel 2016 中運行良好。我們正在嘗試升級代碼以解決問題。 感謝您的評論。
網站主持人對此評論進行了最小化
您好,感謝您的代碼。 我沒有收到任何錯誤消息,但該公式只能像普通的 vlookup 一樣工作。 你能幫忙嗎? 謝謝你的時間。
網站主持人對此評論進行了最小化
你好

我也遇到了同樣的問題,請問您知道怎麼解決了嗎?

謝謝!
網站主持人對此評論進行了最小化
嗨,我收到錯誤“編譯錯誤:檢測到模棱兩可的名稱:xDic
網站主持人對此評論進行了最小化
嗨,我收到錯誤“編譯錯誤:檢測到模棱兩可的名稱:xDic
網站主持人對此評論進行了最小化
嗨,我是使用 VBA 的新手,並嘗試在我的電子表格中使用此代碼,但是在使用查找時,Rec2 選項卡上的文本格式不會出現在 Rec 選項卡上。 任何幫助將不勝感激。 謝謝帕特
網站主持人對此評論進行了最小化
這是文件和圖片
網站主持人對此評論進行了最小化
我得到相同的模棱兩可的名稱錯誤 - 有沒有人設法解決它?
網站主持人對此評論進行了最小化
我得到相同的模棱兩可的名稱錯誤 - 有沒有人設法解決它?
這裡還沒有評論
載入更多
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點

關注我們

版權所有©2009 - 萬維網。extendoffice.com。 | 版權所有。 供電 ExtendOffice。 |
Microsoft和Office徽標是Microsoft Corporation在美國和/或其他國家的商標或註冊商標。
受Sectigo SSL保護