Erreur d'exécution '1004': La méthode 'Range' de l'objet '_Worksheet' a échoué

[zuli3n]

Bonjour le forum,

Je viens vers vous aujourd'hui car j'ai un problème que je n'avais pas auparavant.
Après avoir cherché de mon coté je me suis rendu compte que j'avais cette erreur quand j'avais trop de valeurs à mettre dans une variable, voici le bout de code:

Private Sub Worksheet_Change(ByVal Target As Range)

'If zSurv = "" Then Exit Sub

' Ci-dessus ça fonctionne, moins de 41 valeurs
'zSurv = "$C$9,$L$9,$U$9,$AD$9,$C$14,$L$14,$U$14,$AD$14,$C$19,$L$19,$U$19,$AD$19,$C$24,$L$24,$U$24,$AD$24,$C$29,$L$29,$U$29,$AD$29,$C$34,$L$34,$U$34,$AD$34,$C$39,$L$39,$U$39,$AD$39,C$44,$L$44,$U$44,$AD$44,$C$49,$L$49,$U$49,$AD$49,$C$54,$L$54,$U$54,$AD$54,$C$59"

' Ci-dessous ça ne fonctionne plus, plus de 41 valeurs
zSurv = "$C$9,$L$9,$U$9,$AD$9,$C$14,$L$14,$U$14,$AD$14,$C$19,$L$19,$U$19,$AD$19,$C$24,$L$24,$U$24,$AD$24,$C$29,$L$29,$U$29,$AD$29,$C$34,$L$34,$U$34,$AD$34,$C$39,$L$39,$U$39,$AD$39,C$44,$L$44,$U$44,$AD$44,$C$49,$L$49,$U$49,$AD$49,$C$54,$L$54,$U$54,$AD$54,$C$59,$AD$80"

Dim CellSurv As Range

Set CellSurv = Range(zSurv)

If Not (Application.Intersect(Target, CellSurv) Is Nothing) Then
    ChoixHeure
End If

End Sub

Avez-vous une astuce dans votre trousse ?
En vous remerciant d'avance,
Cordialement.

 

[zuli3n]

Bonjour le forum,

Juste pour vous dire que j'ai réussi à résoudre mon problème, malgré plusieurs essais j'ai pas pu intégrer votre piste Staple1600, dans mon bout de code. J'ai donc opté pour la solution de Paf, c'est un peu barbare, mais ça le mérite de fonctionner, car je cible tout en dessous des cellules à partir de C9 L9 U9 AD9 et AM9 quand le mois à 5 semaines. Oui en effet j'ai pas réussi à faire une belle boucle donc bon ...

Voici le massacre:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CellSurv As Range
Dim LastRow As Long

Set l = Sheets("Planning").Range("G1")

With Sheets("Planning")
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
End With
    
LastRow = LastRow - 1
    
If LastRow = 1 Then Exit Sub

If l = 5 Then
    Set r1 = Range("C9:C" & LastRow)
    Set r2 = Range("L9:L" & LastRow)
    Set r3 = Range("U9:U" & LastRow)
    Set r4 = Range("AD9:AD" & LastRow)
    Set r5 = Range("AM9:AM" & LastRow)
    Set CellSurv = Union(r1, r2, r3, r4, r5)
Else
    Set r1 = Range("C9:C" & LastRow)
    Set r2 = Range("L9:L" & LastRow)
    Set r3 = Range("U9:U" & LastRow)
    Set r4 = Range("AD9:AD" & LastRow)
    Set CellSurv = Union(r1, r2, r3, r4)
End If

If Not (Application.Intersect(Target, CellSurv) Is Nothing) Then
    ChoixHeureBeta
End If

End Sub

Merci pour votre aide et vos messages.
Cordialement.

 

[Dranreb]

Bonjour.
Ça reste quand même bien plus compliqué que ce que j'avais proposé au poste #13 !
Vous pourriez certes y remplacer les 30 et 80 par des LastRow et LastColumn préalablement calculés.

 

[zuli3n]

Bonsoir le forum, Dranreb

Bonjour.
Ça reste quand même bien plus compliqué que ce que j'avais proposé au poste #13 !
Vous pourriez certes y remplacer les 30 et 80 par des LastRow et LastColumn préalablement calculés.

Oui j'avoue ce n'est pas du tout élégant, mais j'ai pas réussi à intégrer votre bout de code pour alimenter CellSurv en range, mes compétences me le permettent pas, comme j'étais bloqué j'ai fait ce que j'ai pu.

Cordialement.

 

[Dranreb]

Pourquoi avez vous besoin de ce CellSurv ? Il est à mon avis inutile puisqu'on abandonne la procédure si Target n'est pas concerné.

 

[zuli3n]

Pourquoi avez vous besoin de ce CellSurv ? Il est à mon avis inutile puisqu'on abandonne la procédure si Target n'est pas concerné.

Dranreb,

Parceque j'avais récupéré un bout de code sur le net qui l'utilise, alors je me suis adapté. :

If Not (Application.Intersect(Target, CellSurv) Is Nothing) Then

Je vais faire d'autres essais demain à tête reposé, afin de supprimer ce CellSurv, car j'avoue si je peux optimiser et apprendre quelque chose, je prends.
Mais à votre avis je peux utiliser votre idée en utilisant plutôt ça ?:

If Not (Application.Intersect(Target) Is Nothing) Then

Merci Dranreb, cordialement.

 

[Dranreb]

Non, aucun test, juste ChoixHeureBeta, systématiquement. On est sorti de la procédure plus haut par un Exit Sub si Target n'est pas concerné.