Error 1004 using range in excel vba

Question

I have a doubt about the use of a range, because I get an error with similar combinations (edited: in all cases the sheet "GOOSES" is not the active sheet):

dim rango as range

Set rango = Sheets("GOOSES").Range(Cells(5, 7), Cells(5, 11)) --> Error 1004
Set rango = Range(Cells(5, 7), Cells(5, 11)) --> Ok
Set rango = Sheets("GOOSES").Range("G7:K7") --> Ok

How is possible when I use the combination of sheet + range with cells it is not working but if I use sheet + range with literal strings is working?

Thanks and regards.

Answer 1

Hi @Iván de Pablo

You did not mention that you get the error when sheet GOOSES isn't the active sheet :) - that should have given you an indication. Cells is a Worsheet property, so the following will do what you want:

With Worksheets("GOOSES")  
    Set rango = Range(.Cells(5, 7), .Cells(5, 11))  
End With  

Or (not a good practice):

Set rango = Range(Worksheets("GOOSES").Cells(5, 7), Worksheets("GOOSES").Cells(5, 11))  

Answer 2

Hello Lz-3068,

You are right, I forgot this detail. In all test the "GOOSES" worksheet was not the active sheet (I will add it)

As I see in your example the problem is I can't use "Cells" property with the complete path if the active sheet is not the destination sheet.

As I didnt get it I used the change from number to character with Split(Cells(1, [row]).Address, "$")(1) in order to use: Set rango = Sheets("GOOSES").Range("G7:K7")

Anyway thank you for your info.