Run=time error '1004': Method 'Range of Object'_Global' failed

[2077delta]

I'm getting the runtime error whent I try to select a range using a defined name and not the actual range reference. I name my variables, then use the set command to define the range of the name(s). When the code selects the range using cell references, everything works . When I select the range using the name I defined, I get the error. It's very frustrating. My code is below. Also, if there is a more efficient way to write the code, please feel free to edit. Thank you.

Sub CVCB_dnld2()
'
Dim myRows As Integer
Dim myRange As Range
Dim myDrCr As Range
Dim myOrder As Range

' Format and Sort CVCB Activity Dowload

myRows = Worksheets("Sheet1").Range(Range("a2"), Range("a2").End(xlDown)).Rows.Count
Set myRange = Worksheets("Sheet1").Range(Range("A2"), Range("A2").Offset(myRows - 1, 10))
Set myDrCr = Worksheets("Sheet1").Range(Range("E2"), Range("E2").Offset(myRows - 1, 1))
Set myOrder = Worksheets("Sheet1").Range(Range("K2"), Range("K2").Offset(myRows - 1, 0))

Worksheets("Sheet1").Range(Range("a2"), Range("a2").End(xlDown)).Select 'Works as intended
Worksheets("Sheet1").Range(Range("E2"), Range("E2").Offset(myRows - 1, 1)).Select 'Works as intended
Worksheets("Sheet1").Range(Range("K2"), Range("K2").Offset(myRows - 1, 0)).Select 'Works as intended

Range(myRange).Select 'Gets runtime error
Range(myDrCr).Select 'Gets runtime error
Range(myOrder).Select 'Gets runtime error

 

[DanteAmor]

Change this

Range(myRange).Select [B]'Gets runtime error[/B]
Range(myDrCr).Select [B]'Gets runtime error[/B]
Range(myOrder).Select [B]'Gets runtime error[/B]

To this:

  myRange.Select
  myDrCr.Select
  myOrder.Select

 

[Anthony47]

myRange (as well ast the other 2 variables) are already "Ranges"; so try

myRange.Select

But this assumes that Sheet1 is the selected sheet, or you will still get a run time error.
And ...are you sure you need to "select" those ranges? (in most of the cases you may work on a range without selecting it)

EDIT: As already recommanded by DM

 

[DanteAmor]

Also consider that if you are on another sheet than Sheet1, then you must reference the sheet in all range objects:

For example:

Sub CVCB_dnld2()
'
  Dim myRows As Integer
  Dim myRange As Range
  Dim myDrCr As Range
  Dim myOrder As Range
  
  ' Format and Sort CVCB Activity Dowload
  
  myRows = Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("a2"), Worksheets("Sheet1").Range("a2").End(xlDown)).Rows.Count
  Set myRange = Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("A2"), Worksheets("Sheet1").Range("A2").Offset(myRows - 1, 10))
  Set myDrCr = Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("E2"), Worksheets("Sheet1").Range("E2").Offset(myRows - 1, 1))
  Set myOrder = Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("K2"), Worksheets("Sheet1").Range("K2").Offset(myRows - 1, 0))
  
  Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("a2"), Worksheets("Sheet1").Range("a2").End(xlDown)).Select 'Works as intended
  Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("E2"), Worksheets("Sheet1").Range("E2").Offset(myRows - 1, 1)).Select 'Works as intended
  Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("K2"), Worksheets("Sheet1").Range("K2").Offset(myRows - 1, 0)).Select 'Works as intended
  
  myRange.Select
  myDrCr.Select
  myOrder.Select

End Sub

Or in an abbreviated way.
Note how to select a range, you must first be on the sheet where the object to be selected is located.

Sub CVCB_dnld3()
'
  Dim myRows As Integer
  Dim myRange As Range
  Dim myDrCr As Range
  Dim myOrder As Range
  
  ' Format and Sort CVCB Activity Dowload
  With Sheets("Sheet1")
    myRows = .Range(.Range("a2"), .Range("a2").End(xlDown)).Rows.Count
    Set myRange = .Range(.Range("A2"), .Range("A2").Offset(myRows - 1, 10))
    Set myDrCr = .Range(.Range("E2"), .Range("E2").Offset(myRows - 1, 1))
    Set myOrder = .Range(.Range("K2"), .Range("K2").Offset(myRows - 1, 0))
    
    .Select
    
    .Range(.Range("a2"), .Range("a2").End(xlDown)).Select 'Works as intended
    .Range(.Range("E2"), .Range("E2").Offset(myRows - 1, 1)).Select 'Works as intended
    .Range(.Range("K2"), .Range("K2").Offset(myRows - 1, 0)).Select 'Works as intended
    
    myRange.Select
    myDrCr.Select
    myOrder.Select
  End With
  
End Sub

Regards
Dante Amor

 

[2077delta]

Change this

Range(myRange).Select [B]'Gets runtime error[/B]
Range(myDrCr).Select [B]'Gets runtime error[/B]
Range(myOrder).Select [B]'Gets runtime error[/B]

To this:

  myRange.Select
  myDrCr.Select
  myOrder.Select

Thank you, that worked. I was only selecting those ranges so I could see it was picking up the correct ranges. I usually try to avoid using Select when writing the actual code.