Error 1004 using range in excel vba

Question

question

IvndePablo-5641 asked Feb 12, '21 emizhang-msft commented Feb 25, '21

Error 1004 using range in excel vba

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.

office-vba-dev office-excel-itpro

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

emizhang-msft · Feb 15, 2021 at 07:54 AM

Hi,

Thanks for accepting answer here. Other partners who read the forums with the same issue can get more information from the correct result.

0 ·

Answer 1 · 2021-02-13T08:01:16.333Z

Lz-3068 answered Feb 13, '21

Hi @IvndePablo-5641

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-02-13T11:19:06.44Z

IvndePablo-5641 answered Feb 13, '21 Lz-3068 commented Feb 13, '21

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.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 · Feb 13, 2021 at 12:26 PM

Glad I could help @IvndePablo-5641. To help others with a similar issue please Accept as answer my reply with the code. Thanks in advance & nice day...

0 ·

question