כיצד לחפש/למצוא ערכים בחוברת עבודה אחרת?

ייתכן שיהיה מעט מבלבל להגדיר את נתיב השמירה, שם הקובץ, והגיליון בפונקציית VLOOKUP. שיטה זו תציג VBA כדי לפתור זאת בקלות.

1. לחץ על מקשי Alt + F11 כדי לפתוח את חלון Microsoft Visual Basic for Applications.

2. לחץ על Insert > Module, ולאחר מכן הדבק את קוד ה-VBA הבא לחלון המודול הפתוח.

VBA: חיפוש נתונים והחזרת ערכים מחוברת עבודה סגורה אחרת

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 או לחץ על כפתור Run כדי להריץ את ה-VBA.

4. בחלון הדו-שיח הפתוח, אנא ציין את טווח הנתונים שתחפש, ולחץ על אישור כפתור.

5. כעת בבקשה בחר את חוברת העבודה שבה תחפש ערכים בחלון הדו-שיח הפתוח Select a Workbook, ולחץ על פתח כפתור.

כעת כל הערכים שנבחרו נמצאו בחוברת העבודה הסגורה שצוינה, והערכים המתאימים מוחזרים בעמודה שצוינה. ראה צילום מסך: