title: Excel VBA populate list box with searched dynamic range
body: <p>My difficulty is this: I have a Excel spreadsheet where I need a UserForm ListBox to populate itself with some rows of data depending on previous user input. </p>
  
  <p>So the UserForm should pick up the user's input in <code>Sheet1</code> (say it's <code>123abc</code>) and then search for similar results in <code>Sheet2</code>. The nearest results (probably a range of about five similar results) should then fill the listbox.</p>
  
  <p>So it could return something like <code>123a</code>, <code>123ab</code>, <code>1235c</code>, and <code>1355abc</code> from the list</p>
  
  <p>Hope this makes sense - thanks for your help in advance.</p>
  
  <p>The code I've tried (and doesn't work, turns a 1004) with notes for clarification:</p>
  
  <pre><code>Private Sub UserForm_Initialize()
    Dim cs As Worksheet
    Dim c As Range
    Dim i As Integer
    Dim code As Variant
    Dim co As String
  
  Set code = Sheets("Sheet1").Range("B4") 'picks up user input
  co = Left(code, 2) 'just takes first two letters of input to find similar
  
  Set cs = Sheets("Sheet2")
  Set c = cs.Range("A2:A20000").Find(co) 'find code (obviously :))
  
  For i = 1 To cs.Cells(c.Offset(5, 0), 1).End(xlUp).Row Step 1
     If cs.Cells(i, 1).Value &lt;&gt; vbNullString Then Me.ListBox1.AddItem cs.Cells(i, 1).Value
  Next i 'populate listbox
  
  End Sub
  </code></pre>
  
  <p>Many thanks!</p>
  
  <hr>
  
  <h2>EDIT</h2>
  
  <p>I have marked the below as the accepted answer, but for others browsing this, the problem still exists as to integrating this with the template Excel files, as it returns the error 'External table is not in the expected format'. I had to change <code>ThisWorkbook.FullName</code> to the <strong>full template path</strong> to remove another error too.</p>