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.

 

[sylvanu]

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.

 

[Hasco]

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,

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:

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]

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.

 

[Hasco]

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]

Bonjour à tous,

une solution consisterait à scinder la plage en deux variables puis à regrouper dans une 3ème

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]

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]

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]

Bonjour le fil

Pas d'erreur chez moi
(sans doute parce que je paie mes bières en € et pas en $ )

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]

Bonjour à tous,

une solution consisterait à scinder la plage en deux variables puis à regrouper dans une 3ème

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]

Bonjour le fil

Pas d'erreur chez moi
(sans doute parce que je paie mes bières en € et pas en $ )

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

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

 

[Staple1600]

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]

Bonsoir.
Ne pourriez vous pas tester plutôt par un code de ce genre si Target est concerné ?

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]

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)

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]

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)

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.