title: VBA VLookup allow the user to select the filename on directory
body: <p>I have been attempting to insert a <code>VLookup</code> formula into a column using VBA. The <code>Table_array</code> is variable and changes each month, so I would like a dialog box to specify the file I want to use. The <code>Table_array</code> is in an identical format each month and my formula so far is as follows:</p>
  
  <pre><code>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 (*.xls), *.xls")
  
  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(" &amp; Cells(FirstRow, myValues.Column) &amp; ", 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
  </code></pre>
  
  <p>I'm getting an issue with 'myFile' at the moment where the code doesn't proceed past the <code>Set = myFile</code> line. Any suggestions or amendments would be most welcome. Other than that the variable file will never be longer than 20000 lines (which is why I have fixed the range in the formula) and we will always pick up column 5.</p>