Error 1004 using range in excel vba

Iván de Pablo 21 Reputation points
2021-02-12T22:07:59.053+00:00

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.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,649 questions
Accepted answer
  1. Lz._ 8,991 Reputation points
    2021-02-13T08:01:16.333+00:00

    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))
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Iván de Pablo 21 Reputation points
    2021-02-13T11:19:06.44+00:00

    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.