[發問] VBA VLookup允許用戶選擇目錄上的文件名

論壇贊助勳章

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2019-12-1 18:30 | 只看該作者

我在在網絡上找到這個問題(看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

複製代碼

靜思自在 : 能善用時間的人，必能掌握自己努力的方向。