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-28T10:33:29.363

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 '17 at 10:33

answered Sep 28 '17 at 09:30

FunThomas

23,043
3
18
34

Thank you for your answer ! – Glork Sep 28 '17 at 09:38
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. – Darren Bartrup-Cook Sep 28 '17 at 09:42
For more detail on avoiding `Select`, see https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – aucuparia Sep 28 '17 at 10:38

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

1 Answers1