返回列表 上一主題 發帖

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

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

我在在網絡上找到這個問題(看PartA) 他們說: Set myFile = Application.GetOpenFilename 不能回傳一個object.
之後有人解答要使用以下B部份.去修改...可是我不懂要如何修改 , 有人能試一下嗎?
  1. Sub VlookupMacro()

  2. Dim FirstRow As Long
  3. Dim FinalRow As Long
  4. Dim myValues As Range
  5. Dim myResults As Range
  6. Dim myFile As Range
  7. Dim myCount As Integer

  8. Set myFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")

  9. Set myValues = Application.InputBox("Please select the first cell in the column with the values that you're looking for", Type:=8)

  10. Set myResults = Application.InputBox("Please select the first cell where you want your lookup results to start ", Type:=8)

  11. Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = _
  12.     "=VLOOKUP(" & Cells(FirstRow, myValues.Column) & ", myFile.value($A$2:$B$U20000), 5, False)"

  13. If MsgBox("Do you want to convert to values?", vbYesNo) = vbNo Then Exit Sub

  14. Columns(myResults.Column).Copy
  15. Columns(myResults.Column).PasteSpecial xlPasteValues
  16. Application.CutCopyMode = False

  17. End Sub
複製代碼
===============================================
B部:
  1. FileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")
  2. If FileName = False Then
  3.      ' User pressed Cancel
  4.     MsgBox "Please select a file"
  5.     Exit Sub
  6. Else
  7.     Workbooks.Open Filename:=FileName  
  8. End If
複製代碼

vlookup.zip (21.25 KB)

        靜思自在 : 生氣,就是拿別人的過錯來懲罰自己。
返回列表 上一主題