title: VBA - Set Range Between Two Dates Using Search Function
body: <p>I'm trying to get my VBA code to search through a column for a user-inputted value (on a form) and set a range based on the values.</p>
  
  <p>I need the code to scan DOWN through the column until it finds the value (which is a date) and then scan UP through the column to get the second part of the range. I need it to be like this because there might be multiple instances of the same date and they all need to be accounted for.</p>
  
  <p>I've tried this:</p>
  
  <pre><code>StartRange = ws.Cells.Find(What:=StartDate, SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues)
  
  EndRange = ws.Cells.Find(What:=EndDate, SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues)
  </code></pre>
  
  <p>But it's not working the way I had expected and is erroring. (Edit: the WS has been defined, so I know that's not the issue). I don't even know if I'm going about this the right way</p>
  
  <p>I'm feeling defeated :(</p>
  
  <p>Any help would be appreciated, Thanks in advance!</p>
  
  <p>Edit:</p>
  
  <p>I've yet to try any of the suggestions as I am away from my project at the moment, but I feel I need to clarify a few things. </p>
  
  <ol>
  <li><p>The dates will always be in chronological order, I have a script that organises them on sheet activation</p></li>
  <li><p>I need to be able to error handle dates that do not appear in the database, I also need the script to be able to "skip over" dates that don't exist. Ie, 1st 1st 1st, 3rd, 3rd, 5th. If my start and end dates were the 1st and 5th, the entire example would be the range. </p></li>
  </ol>
  
  <p>Thanks for your help so far guys though, I appreciate it!</p>
  
  <p>EDIT2:</p>
  
  <p>I've tried a few answers and have added this in to my code, but it is now failing on a Range_Global fail.</p>
  
  <pre><code>Dim startrange, endrange, searchrange As Range
  LookUpColumn = 2
  
  With ws.Columns(LookUpColumn)
      Set startrange = .Find(What:=Me.R_Start.Value, _
        After:=ws.Cells(.Rows.count, LookUpColumn), _
        SearchOrder:=xlRows, _
        SearchDirection:=xlNext, LookIn:=xlValues)
  
      Set endrange = .Find(What:=Me.R_End.Value, _
        After:=ws.Cells(5, LookUpColumn), _
        SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues)
  
      searchrange = Range(startrange, endrange)
  
  MsgBox searchrange.Address
  End With
  </code></pre>
  
  <p>Any suggestions?</p>