- 帖子
- 17
- 主題
- 7
- 精華
- 0
- 積分
- 24
- 點名
- 0
- 作業系統
- windwo10
- 軟體版本
- excel2013
- 閱讀權限
- 10
- 註冊時間
- 2017-1-7
- 最後登錄
- 2023-2-18

|
[發問] VBA VLookup允許用戶選擇目錄上的文件名
我在在網絡上找到這個問題(看PartA) 他們說: Set myFile = Application.GetOpenFilename 不能回傳一個object.
之後有人解答要使用以下B部份.去修改...可是我不懂要如何修改 , 有人能試一下嗎?- Sub VlookupMacro()
- Dim FirstRow As Long
- Dim FinalRow As Long
- Dim myValues As Range
- Dim myResults As Range
- Dim myFile As Range
- Dim myCount As Integer
- Set myFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
- Set myValues = Application.InputBox("Please select the first cell in the column with the values that you're looking for", Type:=8)
- Set myResults = Application.InputBox("Please select the first cell where you want your lookup results to start ", Type:=8)
- Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = _
- "=VLOOKUP(" & Cells(FirstRow, myValues.Column) & ", myFile.value($A$2:$B$U20000), 5, False)"
- If MsgBox("Do you want to convert to values?", vbYesNo) = vbNo Then Exit Sub
- Columns(myResults.Column).Copy
- Columns(myResults.Column).PasteSpecial xlPasteValues
- Application.CutCopyMode = False
- End Sub
複製代碼 ===============================================
B部:- FileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")
- If FileName = False Then
- ' User pressed Cancel
- MsgBox "Please select a file"
- Exit Sub
- Else
- Workbooks.Open Filename:=FileName
- End If
複製代碼 |
-
-
vlookup.zip
(21.25 KB)
|