title: Store multiple results from HLookup in listbox
body: <p>I am trying to store the results from a HLookup function in a listbox. However, it shows an error 1004 at the row 'set Myrange = ...'. </p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  
  'fill listbox with athletes from relevant country
  Dim Myrange As Range
  Dim Test(4 To 24) As String
  Dim name As String
  Dim i As Integer
  
  name = Sheets("RelayRankingData").Cells(2, 5)
  Set Myrange = Sheets("AthletesTable").Range("A1:L24")
  Myrange.Select
  For i = 4 To 24
  
  Test(i) = Application.WorksheetFunction.HLookup(name, Myrange, i, False)
  
  Next i
  
  UserForm1.ListBox1.List = Test()
  '-------------------------------------
  
  'Show form with listbox and transferbutton
  UserForm1.Show
  
  End Sub
  </code></pre>
  
  <p>I also added some screenshots from the data [Screenshot1][Screenshot 2]</p>