title: Object Required - 424 Error when trying to set value to a range
body: <p>I am trying to take a cell's reference location (which can easily change) and have that location be referenced in an filtered list of unique records, which is automatically updating when the user initializes the UserForm.</p>
  
  <p>On Sheet 9 (Helpers) cell AI2 returns the absolute location of a cell on Sheet 1 (SA Payroll Dist Sheet). AI2 contains the following formula:</p>
  
  <pre><code>=CELL("address",INDEX('SA Payroll Dist Sheet'!B:B,MATCH(Helpers!AH2,'SA Payroll Dist Sheet'!B:B,0)))
  </code></pre>
  
  <p>then cell AJ2 looks at the last digits of the value in AI2 using:</p>
  
  <pre><code>=RIGHT(AI2,6)
  </code></pre>
  
  <p>This correctly returns the value </p>
  
  <p><strong>$B$547</strong> (which is the cell reference I want)</p>
  
  <p>Here's what happens when I try to initialize the UserForm:</p>
  
  <p>Run-time error '424':
  Object Required</p>
  
  <p>For the following:</p>
  
  <pre><code>Dim ER As Range 
  
    'I want 'ER' to represent the value in AJ2
  
  Set ER = ActiveWorkbook.Sheets(9).Range("AJ2").Value 
  
    'Here's where debugger highlights yellow
  
  
  Worksheets("SA Payroll Dist Sheet").Range("$B$15:ER").Select 
  
    ' Is this even the proper way to refer to the range I am after? 
    ' In this case, the range would be ("$B$15:$B$457")
  
  
  Selection.Copy 
  Sheet9.Range("AK2").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range _
      ("AK2"), Unique:=True 
  
    ' Here's where I want the list of unique records 
    ' in Sheet1.Range("$B$15:$B$457") to appear
  </code></pre>
  
  <p>The debugger highlights the following line:</p>
  
  <pre><code>Set ER = ActiveWorkbook.Sheets(9).Range("AJ2").Value
  </code></pre>
  
  <p>I've tried:</p>
  
  <pre><code>Set ER = Sheet9.Range("AJ2").Value
  </code></pre>
  
  <p>and</p>
  
  <pre><code>Set ER = Worksheets("Helpers").Range("AJ2").Value
  </code></pre>
  
  <p>but I get the same error. I feel like the solution to this is going to be embarrassingly simple, but I've run out of ideas. I've tried solutions from other questions on the same error, but nothing seems to fix the issue I'm having.</p>