ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VBA VLookup¤¹³\¥Î¤á¿ï¾Ü¥Ø¿ý¤Wªº¤å¥ó¦W

[µo°Ý] VBA VLookup¤¹³\¥Î¤á¿ï¾Ü¥Ø¿ý¤Wªº¤å¥ó¦W

§Ú¦b¦bºôµ¸¤W§ä¨ì³o­Ó°ÝÃD(¬ÝPartA) ¥L­Ì»¡: Set myFile = Application.GetOpenFilename ¤£¯à¦^¶Ç¤@­Óobject.
¤§«á¦³¤H¸Ñµª­n¨Ï¥Î¥H¤UB³¡¥÷.¥h­×§ï...¥i¬O§Ú¤£À´­n¦p¦ó­×§ï , ¦³¤H¯à¸Õ¤@¤U¶Ü?
  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
½Æ»s¥N½X
===============================================
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
½Æ»s¥N½X

vlookup.zip (21.25 KB)

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD