อาจเป็นเรื่องที่สับสนในการกำหนดค่าเส้นทางการบันทึกชื่อไฟล์และแผ่นงานในฟังก์ชัน VLOOKUP วิธีนี้จะแนะนำ VBA เพื่อแก้ปัญหาได้อย่างง่ายดาย

1 กด อื่น ๆ + F11 ปุ่มเพื่อเปิดหน้าต่าง Microsoft Visual Basic for Applications

2 คลิกที่ สิ่งที่ใส่เข้าไป > โมดูลจากนั้นวางโค้ด VBA ด้านล่างลงในหน้าต่างโมดูลการเปิด

VBA: ข้อมูล Vlookup และส่งคืนค่าจากสมุดงานอื่นที่ปิดอยู่

Private Function GetColumn(Num As Integer) As String
If Num <= 26 Then
GetColumn = Chr(Num + 64)
Else
GetColumn = Chr((Num - 1) \ 26 + 64) & Chr((Num - 1) Mod 26 + 65)
End If
End Function
Sub FindValue()
Dim xAddress As String
Dim xString As String
Dim xFileName As Variant
Dim xUserRange As Range
Dim xRg As Range
Dim xFCell As Range
Dim xSourceSh As Worksheet
Dim xSourceWb As Workbook
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xUserRange = Application.InputBox("Lookup values :", "Kutools for Excel", xAddress, Type:=8)
If Err <> 0 Then Exit Sub
Set xUserRange = Application.Intersect(xUserRange, Application.ActiveSheet.UsedRange)
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Application.ScreenUpdating = False
Set xSourceWb = Workbooks.Open(xFileName)
Set xSourceSh = xSourceWb.Worksheets.Item(1)
xString = "='" & xSourceWb.Path & Application.PathSeparator & _
"[" & xSourceWb.Name & "]" & xSourceSh.Name & "'!$"
For Each xRg In xUserRange
Set xFCell = xSourceSh.Cells.Find(xRg.Value, , xlValues, xlWhole, , , False)
If Not (xFCell Is Nothing) Then
xRg.Offset(0, 2).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row
End If
Next
xSourceWb.Close False
Application.ScreenUpdating = True
End Sub

หมายเหตุ: VBA นี้จะส่งคืนค่าในคอลัมน์ซึ่งเป็น 2 คอลัมน์หลังคอลัมน์ที่เลือก ตัวอย่างเช่นฉันเลือกคอลัมน์ B เมื่อใช้ VBA นี้ค่าจะกลับมาในคอลัมน์ D หากคุณต้องการเปลี่ยนคอลัมน์ปลายทางโปรดดูรหัส xRg.Offset (0, 2) .Formula = xString & GetColumn (xFCell.Column + 1) & "$" & xFCell.Row และแทนที่ 2 ไปยังหมายเลขอื่น ๆ ตามที่คุณต้องการ

3 กด F5 หรือคลิกปุ่ม วิ่ง เพื่อเรียกใช้ VBA นี้

4. ในกล่องโต้ตอบเปิดโปรดระบุช่วงข้อมูลที่คุณจะค้นหาและคลิกไฟล์ OK ปุ่ม

5. ตอนนี้โปรดเลือกสมุดงานที่คุณจะค้นหาค่าในการเปิดกล่องโต้ตอบเลือกสมุดงานและคลิกที่ จุดเปิด ปุ่ม

ตอนนี้ค่าที่เลือกทั้งหมดจะถูกค้นหาในสมุดงานปิดที่ระบุและค่าที่เกี่ยวข้องจะถูกส่งกลับในคอลัมน์ที่ระบุ ดูภาพหน้าจอ: