Object Required - 424 Error when trying to set value to a range

Question

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.

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:

=CELL("address",INDEX('SA Payroll Dist Sheet'!B:B,MATCH(Helpers!AH2,'SA Payroll Dist Sheet'!B:B,0)))

then cell AJ2 looks at the last digits of the value in AI2 using:

=RIGHT(AI2,6)

This correctly returns the value

$B$547 (which is the cell reference I want)

Here's what happens when I try to initialize the UserForm:

Run-time error '424': Object Required

For the following:

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

The debugger highlights the following line:

Set ER = ActiveWorkbook.Sheets(9).Range("AJ2").Value

I've tried:

Set ER = Sheet9.Range("AJ2").Value

and

Set ER = Worksheets("Helpers").Range("AJ2").Value

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.

Answer 1

ER is an object (range) variable so you assign the range to the variable. The value is a property of the variable so you access that property when you need it (Value is the default property so you don't strictly need to specify it but it is good practice to do so). You cannot assign the value of a range to a range variable.

You also need to take the ER outside the quotes in the next line.

Not sure why you are trying to do with the Select/Copy so have left in but in general best to avoid Select.

Sub x()

Dim ER As Range

'I want 'ER' to represent the value in AJ2

Set ER = ActiveWorkbook.Sheets(9).Range("AJ2")

'Here's where debugger highlights yellow

Worksheets("SA Payroll Dist Sheet").Range("$B$15:" & ER.Value).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

End Sub

Answer 2

Thank you SJR, your comments and changes led me to a working solution!

I didn't know how to post the code in the comment portion, so here's what I ended up using:

Dim ER As Range
Set ER = Sheet9.Range("AJ2")
Sheet1.Range("$B$15:" & ER.Value).AdvancedFilter Action:=xlFilterCopy, 
CopyToRange:=Range _
    ("AK2"), Unique:=True

As you can see, I also referenced the wrong range ("AK2") in my filter line, which I've now corrected as well ("$B$15:" & ER.Value)