vba excel : runtime error 1004 method range of object _global failed

Question

I'm having the error "runtime error 1004 method range of object _global failed" when I launched the following macro :

Dim nameDebut As Range, nameFin As Range ' <- my global variable
sub mySub()
...
   Set nameDebut = Range("A1").Offset(0, 1)
   Set nameFin = Range("A1").Offset(0, 20)
   Range("nameDebut:nameFin").Select ' <- fail occurs here
...

So I would like to select this range of cells like this. Is it possible ? Any workaround ?

Tx

FunThomas · Accepted Answer · 2017-09-28 10:33:29Z

1

Just change the last line to

Range(nameDebut,nameFin).Select

Not discussing your code, but using select is nearly never needed in VBA. Maybe this can help:

dim myRange as Range
set myRange = Range(nameDebut,nameFin)
' Now use `myRange` for whatever you want to do...

edited Sep 28, 2017 at 10:33

answered Sep 28, 2017 at 9:30

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Darren Bartrup-Cook Over a year ago

Does it work with that Select on the end? Shouldn't it just be set myRange = Range(nameDebut,nameFin) .... remembering to fully qualify the range reference as well.

aucuparia Over a year ago

For more detail on avoiding Select, see stackoverflow.com/questions/10714251/…

Collectives™ on Stack Overflow

vba excel : runtime error 1004 method range of object _global failed

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related