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

zuli3n

XLDnaute Occasionnel
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:

VB:
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.
 
Dernière édition:

sylvanu

XLDnaute Impliqué
Supporter XLD
Bonjour Zuli, vous êtes sous quelle version XL ?
Normalement la limite d'une chaine de caractère est très élevée.
Or là votre première chaine qui ne marche pas fait 257 caractères, celle qui marche 250. Peut être une limite à 255 caractères. Bizarre.
 

Roblochon

XLDnaute Accro
Bonjour,

Si vous ne voulez traiter qu'une cellule à la fois , tester si l'adresse de la cellule est dans la chaine de caractère sSurv,
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const zSurv As String = "$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"
    If InStr(1, zSurv, Target.Address) > 0 Then
        'Faire quelque chose
    End If
End Sub
Cela peut être fait en filtrant un tableau également:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const zSurv As String = "$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"
     
    If UBound(Filter(Split(zSurv, ","), Target.Address)) > -1 Then
        'Faire quelque chose
    End If
End Sub
Entre autres....

Cordialement
 

zuli3n

XLDnaute Occasionnel
Bonjour sylvanu, le forum,

J'use Excel 2013, oui il ma fallut quelque temps pour comprendre de moi même que c'est un problème de valeur, et comme vous dites 257 ce n'est pas grand chose. Merci pour votre retour.
 

Roblochon

XLDnaute Accro
Re, bonjour,

Pas de réponse sur la soluce passée en post 3 :(?

Sinon, et sachant qu'on peut sélectionner 2 147 483 648 cellules discontinues, sélectionnez vos n cellules, nommez la sélection et faites référence à leur nom dans votre code ( If not Intersect( Target, Range("LeNomDeLaSelection")) is nothing then....)

cordialement
 

Paf

XLDnaute Barbatruc
Bonjour à tous,

une solution consisterait à scinder la plage en deux variables puis à regrouper dans une 3ème
VB:
Dim CellSurv As Range

zSurv1 = "$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"
zSurv2 = "$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"

Set CellSurv = Union(Range(zSurv1), Range(zSurv2))
.../...
A+
 

zuli3n

XLDnaute Occasionnel
Bonjour Roblochon, le forum,

Merci pour votre retour. Non je ne veux pas traiter une seule cellule à la fois. En faite la variable zSurv arrive "chargé" d'une autre fonction, avec comme valeur des ranges spécifique. Je n'avais jamais eu se problème auparavant car j'avais moins de valeurs à mettre dans zSurv.
J'ai posté l'exemple de mon problème dû à un grand notre de valeurs, mais j'aurai peut-être dû donner mon fichier entier directement, comme j'ai trouvé l'erreur j'ai voulu faciliter le problème. Si il faut je donnerai mon fichier des mon retour demain soir. Merci bcp Roblochon.
 

sylvanu

XLDnaute Impliqué
Supporter XLD
C'est le nombre de cellules concernées qui est trop élevé. Sur XL2007 j' un "débordement de pile" sur la chaine qui marche pas.
Cela pourrait être une limite, mais je n'ai rien trouvé qui l'explique.
N'y a t'il pas un moyen de regrouper certaines cellules, cela diminuerait le nombre de blocs concernés ?
 

Staple1600

XLDnaute Barbatruc
Bonjour le fil

Pas d'erreur chez moi
(sans doute parce que je paie mes bières en € et pas en $ ;) )
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellSurv As Range, zSurV$
zSurV = "C9,L9,U9,AD9,C14,L14,U14,AD14,C19,L19,U19,AD19,C24,L24,U24,AD24,C29,L29,U29,AD29,C34,L34,U34,AD34,C39,L39,U39,AD39,C44,L44,U44,AD44,C49,L49,U49,AD49,C54,L54,U54,AD54,C59,AD80"
Set CellSurv = Range(zSurV)
If Not Application.Intersect(Target, CellSurv) Is Nothing Then
MsgBox "Test"
End If
End Sub
 

zuli3n

XLDnaute Occasionnel
Bonjour à tous,

une solution consisterait à scinder la plage en deux variables puis à regrouper dans une 3ème
VB:
Dim CellSurv As Range

zSurv1 = "$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"
zSurv2 = "$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"

Set CellSurv = Union(Range(zSurv1), Range(zSurv2))
.../...
A+
Bonjour Paf, le forum,

Merci pour ton retour, mais ou effectivement j'aurais pu scinder en deux mais comme la variable zSurv n'est jamais la même, pas facile à faire.
Demain soir je posterai le fichier entier que je suis en train de monter. Bon je vous préviens d'avance, je ne suis pas un codeur, juste un "bidouilleur" qui fait de son mieux, alors soyez indulgent :) Bon honnêtement je suis surpris que excel n'arrive pas à prendre plus de 255 caractères. Merci à vous pour vos retour.
 

zuli3n

XLDnaute Occasionnel
Bonjour le fil

Pas d'erreur chez moi
(sans doute parce que je paie mes bières en € et pas en $ ;) )
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellSurv As Range, zSurV$
zSurV = "C9,L9,U9,AD9,C14,L14,U14,AD14,C19,L19,U19,AD19,C24,L24,U24,AD24,C29,L29,U29,AD29,C34,L34,U34,AD34,C39,L39,U39,AD39,C44,L44,U44,AD44,C49,L49,U49,AD49,C54,L54,U54,AD54,C59,AD80"
Set CellSurv = Range(zSurV)
If Not Application.Intersect(Target, CellSurv) Is Nothing Then
MsgBox "Test"
End If
End Sub
Bonsoir Staple1600,

Oui effectivement en retirant les $ ça fait bcp moins de caractères, mais le jours ou ma variable "zSurv" arrive avec bcp plus de valeurs, j'aurai le même soucis :) Merci pour votre retour, du coup j'ai envie d'une bière :)

Voici comment est alimenté ma variable zSurv

VB:
Function Surv()

    Dim LastRow As Long
    Dim Txt, xSurv, Survv As String
    Dim i As Integer


zSurv = ""

    With Sheets("Surv")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    
If LastRow = 1 Then Exit Function

    For i = 1 To LastRow
        xSurv = Sheets("Surv").Cells(1 + i, 1)
        If i = LastRow Then
            zSurv = Survv + xSurv
             Exit For
        ElseIf i = LastRow - 1 Then
            Survv = Survv + xSurv
        Else: Survv = Survv + xSurv & ","
        End If
    Next i
    zSurv = zSurv

End Function
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re,

Il y a un petit souci dans ta fonction, non ?
Tu l'as nommée Surv
alors qu'on peut lire (en fin de code)
zSurv=zSurv
 

Dranreb

XLDnaute Barbatruc
Bonsoir.
Ne pourriez vous pas tester plutôt par un code de ce genre si Target est concerné ?
VB:
If (Target.Column - 3) Mod 9 Or Target.Column > 30 Then Exit Sub
If (Target.Row - 9) Mod 5 Or Target.Row > 80 Then Exit Sub
 

Staple1600

XLDnaute Barbatruc
Bonsoir le fil, Dranreb

J'étais parti sur ce truc, puisque c'est dans VBE, je le poste
(et cela rejoint la piste de Dranreb)
VB:
Sub test()
Range(Surv).Interior.Color = vbRed
End Sub
Private Function Surv() As String
Dim i&, strAddress
For i = 3 To 30 Step 9: For j = 9 To 59 Step 5
strAddress = strAddress & "," & Cells(j, i).Address(0, 0)
Next: Next
Surv = Mid(strAddress, 2)
End Function
 

zuli3n

XLDnaute Occasionnel
Re,

Il y a un petit souci dans ta fonction, non ?
Tu l'as nommée Surv
alors qu'on peut lire (en fin de code)
zSurv=zSurv
Bonjour le forum, Dranreb et Staple1600,

Désolé pour cette réponse tardive sur ce file, j'ai du m'absenter précipitant le weekend dernier.
Effectivement y'a un souci en fin de ma fonction Surv, il faut que je reprenne tout ca, je sais que c'est "codé" avec les pieds mais faut que je me bouge à tout revoir, c'est pas évident à mon niveau mais je vais y arriver.

Bonsoir le fil, Dranreb

J'étais parti sur ce truc, puisque c'est dans VBE, je le poste
(et cela rejoint la piste de Dranreb)
VB:
Sub test()
Range(Surv).Interior.Color = vbRed
End Sub
Private Function Surv() As String
Dim i&, strAddress
For i = 3 To 30 Step 9: For j = 9 To 59 Step 5
strAddress = strAddress & "," & Cells(j, i).Address(0, 0)
Next: Next
Surv = Mid(strAddress, 2)
End Function
Effectivement je trouve ce morceau de code vraiment efficace, et mille fois plus optimisé, je l'ai tester, maintenant il faut que je l'adapte car dans mon cas les valeurs "To 30" et "To 59" peuvent changer. Je suis bien conscient qu'il va valoir revoir tout ça, j'aurais cru penser qu'on pouvait stocker largement plus de 255 caractères dans un simple "Set CellSurv = Range(zSurV)".

Dès que j'aurai du temps devant moi je ne manquerai pas de vous faire part de la solution que j'aurai trouvé, je vais dans un premier temps optimiser ma fonction qui alimente ma variable "zSurV" avec les pistes que vous m'avez communiqué. Merci à vous c'est sympa de m'avoir répondu rapidement.

Cordialement.
 

zuli3n

XLDnaute Occasionnel
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:

VB:
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

XLDnaute Barbatruc
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

XLDnaute Occasionnel
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

XLDnaute Barbatruc
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

XLDnaute Occasionnel
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é. :

VB:
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 ?:
VB:
If Not (Application.Intersect(Target) Is Nothing) Then
Merci Dranreb, cordialement.
 

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas