ไปยังเนื้อหาหลัก

วิธีคัดลอกการจัดรูปแบบแหล่งที่มาของเซลล์การค้นหาเมื่อใช้ Vlookup ใน Excel

ในบทความก่อนหน้านี้เราได้พูดถึงการรักษาสีพื้นหลังเมื่อค่า vlookup ใน Excel ในบทความนี้เราจะแนะนำวิธีการคัดลอกการจัดรูปแบบเซลล์ทั้งหมดของเซลล์ผลลัพธ์เมื่อทำ Vlookup ใน Excel กรุณาดำเนินการดังนี้

คัดลอกการจัดรูปแบบแหล่งที่มาเมื่อใช้ Vlookup ใน Excel ด้วยฟังก์ชันที่ผู้ใช้กำหนดเอง


คัดลอกการจัดรูปแบบแหล่งที่มาเมื่อใช้ Vlookup ใน Excel ด้วยฟังก์ชันที่ผู้ใช้กำหนดเอง

สมมติว่าคุณมีตารางตามภาพด้านล่างที่แสดง ตอนนี้คุณต้องตรวจสอบว่าค่าที่ระบุ (ในคอลัมน์ E) อยู่ในคอลัมน์ A หรือไม่และส่งคืนค่าที่สอดคล้องกันด้วยการจัดรูปแบบในคอลัมน์ C โปรดทำดังนี้เพื่อให้บรรลุ

1. ในแผ่นงานมีค่าที่คุณต้องการ vlookup ให้คลิกขวาที่แท็บแผ่นงานแล้วเลือก ดูรหัส จากเมนูบริบท ดูภาพหน้าจอ:

2. ในการเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน โปรดคัดลอกโค้ด 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 Script กล่องใน เอกสารอ้างอิง - VBAProject กล่องโต้ตอบ ดูภาพหน้าจอ:

5 กด อื่น ๆ + Q ปุ่มเพื่อออกจากไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

6. เลือกเซลล์ว่างที่อยู่ติดกับค่าการค้นหาจากนั้นป้อนสูตร =LookupKeepFormat(E2,$A$1:$C$8,3) เข้าไปใน สูตรบาร์แล้วกดปุ่ม เข้าสู่ กุญแจ

หมายเหตุ: ในสูตร E2 มีค่าที่คุณจะค้นหา $ ก $ 1: $ C $ 8 คือช่วงของตารางและตัวเลข 3 หมายความว่าค่าที่สอดคล้องกันที่คุณจะส่งคืนจะอยู่ในคอลัมน์ที่สามของตาราง โปรดเปลี่ยนตามที่คุณต้องการ

7. เลือกเซลล์ผลลัพธ์แรกจากนั้นลาก Fill Handle ลงเพื่อให้ได้ผลลัพธ์ทั้งหมดพร้อมกับการจัดรูปแบบตามที่แสดงภาพด้านล่าง


บทความที่เกี่ยวข้อง:

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์    VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่    สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์    ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...

รายละเอียด


แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
Comments (44)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
It seems to work but excel freezes from it and goes unresponsive. HELP!!!!
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?
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
This comment was minimized by the moderator on the site
This code only works when data is in same sheet.
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.
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.
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...
This comment was minimized by the moderator on the site
Please provide details
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?
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.
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
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?
This comment was minimized by the moderator on the site
I'm getting an error
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?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations