title: Cannot set a range variable without selecting sheet first
body: <p>I'm new to VBA coding and for a project at work I've had to quickly teach myself some VBA. I'm trying to rewrite some code to avoid using the .select command as often as possible. The issue is have is when I define a range as a variable and then attempt to set it.</p>
  
  <pre><code>Dim SearchRng As Range
  
  Set SearchRng = Sheets("Employee Data").Range("B9", Range("B9").End(xlDown).End(xlToRight))
  </code></pre>
  
  <p>The active sheet when this bit of the code is run is not the "Employee Data" sheet and I always get a "Run-time error '1004': Application-defined or object-defined error".</p>
  
  <p>I've found that if I select the "Employee Data" sheet first then set the range then the code works. i.e</p>
  
  <pre><code>Dim SearchRng As Range
  
  Worksheets("Employee Data").Select
  Set SearchRng = Range("B9",Range("B9").End(xlDown).End(xlToRight))
  </code></pre>
  
  <p>This however defeats the purpose of trying to avoid the .select command.</p>
  
  <p>Can anyone explain why the first bit of code doesn't work but then second bit of code does.</p>
  
  <p>Let me know what additional info might be needed to help solve this problem (I'm new to VBA so unsure what is needed).</p>