Run-time 1004 error

[Krull_WarriorKing]

Hello,

I am trying to set a range variable so that it grabs all the columns in a given row. When setting the variable by calling out the cells specifically, the code runs just fine. However, when using the .End(...) extension, the code throws a 1004 error. What's even stranger is that I can copy this exact code (from Sheet1) into a different sheet (Sheet11, for example) and it works just fine. No errors! The lines that throw an error are below. Has anyone else encountered something like this before?

Sub CapacityCompare()

Dim blkI As Range
Dim blkIA As Range
Dim cell As Range
Dim k As Long


Set blkI = Sheets("Capacity").Range(Range("E8"), Range("E8").End(xlToRight))
Set blkIA = Sheets("Capacity").Range(Range("E9"), Range("E9").End(xlToRight))

...
End Sub

I should also say that I have used several variations of this code, such as using With Sheets("Capacity") or using Range(Cells(x,x),Cells(x,Columns.Count).End(...)) with the same results: the code works perfectly in another Sheet, but not in Sheet1.

 

[johnnyL]

You need to specify the sheets for your second 'Range's. Range by itself assumes the active sheet.

Set blkI = Sheets("Capacity").Range(Range("E8"), Range("E8").End(xlToRight))
Set blkIA = Sheets("Capacity").Range(Range("E9"), Range("E9").End(xlToRight))

The 'Range' at the end of those two lines need to have the sheet set like 'Range' at the beginning of those two lines

 

[Krull_WarriorKing]

You need to specify the sheets for your second 'Range's. Range by itself assumes the active sheet.

Set blkI = Sheets("Capacity").Range(Range("E8"), Range("E8").End(xlToRight))
Set blkIA = Sheets("Capacity").Range(Range("E9"), Range("E9").End(xlToRight))

The 'Range' at the end of those two lines need to have the sheet set like 'Range' at the beginning of those two lines

Yup, calling out the correct sheet for the other Range callouts did it. Thank you!

 

[johnnyL]

Glad to help @Krull_WarriorKing.