[VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.[RESOLU]

[Adrien]

Bonjour à tous,

Me revoici avec un problème qui va sûrement paraître simple mais dont la fonction "Recherche" ne m'a pas apporté la solution tellement l'erreur 1004 intervient souvent.

Donc, je suis toujours dans mon long code de Bulletin.

Aujourd'hui, j'ai attaqué l'incrémentation par macro d'une formule matricielle permettant de faire une sorte de moyenne modifiée dont voici la formule originelle pour la première ligne qui se trouve être dans la ligne 7, commençant obligatoirement en C7 mais allant aléatoirement de la ligne C7 à IV7 pour la fin :

=SI(ESTERREUR(SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$G$7)));DECALER(C8;;;1;NB($A$7:$G$7))))/SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$G$7)));DECALER($C$7;;;1;NB($A$7:$G$7))))*10);SI(A13="";"";"Absent");(SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$G$7)));DECALER(C8;;;1;NB($A$7:$G$7))))/SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$G$7)));DECALER($C$7;;;1;NB($A$7:$G$7))))*10))

Formule qui permet donc d'inscrire aussi le texte "Absent" lorsqu'un élève a été absent pour cette matière.


Donc, j'ai ajouté récemment une fonction de TRI par noms des élèves dans mon classeur. Je me suis rendu compte qu'il gardait les points des anciennes places des élèves et ai donc mis une formule pour à chaque fois renouveler les formules.

Voici mon code :

Private Sub CommandButton3_Click()
Sheets("Lire - 1").Range("B8:IV50").Select
 ActiveWorkbook.Worksheets("Lire - 1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Lire - 1").Sort.SortFields.Add Key:=Range("B8"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Lire - 1").Sort
        .SetRange Range("B8:IV50")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
ActiveSheet.Range("IV8").End(xlToLeft).Select
    Selection.FormulaArray = _
        "=IF(ISERROR(SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))),OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))))/SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))),OFFSET(R7C3,,,1,COUNT(R7C1:R7C7))))*10),IF(R[5]C[-3]="""","""",""Absent""),(SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))),OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))))/SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:" & _
        "OFFSET(R7C3,,,1,COUNT(R7C1:R7C7))))*10))"
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell + 40), Type:=xlFillDefault
End Sub

Voilà. Mais j'obtiens une erreur à la ligne de l'incrémentation de la formule

Selection.FormulaArray = _
"=IF(ISERROR(SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))),OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))))/SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))),OFFSET(R7C3,,,1,COUNT(R7C1:R7C7))))*10),IF(R[5]C[-3]="""","""",""Absent""),(SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))),OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))))/SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:" & _
"OFFSET(R7C3,,,1,COUNT(R7C1:R7C7))))*10))"


Erreur 1004

J'ai obtenu ce code par enregistrement de macro, et je ne sais donc pas si il est vraiment potable étant donné que je n'ai encore jamais travaillé sur ce plan en VBA.

Merci à tous,

Adrien

 

[Gorfael]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Salut Adrien et le forum
Dans ton cas, l'erreur 1004 signifie que tu es hors limite de la feuille. Pour prendre un exemple simple :Range("A1").offset(-1,-1) donnera une erreur 1004. Ou si sur la version Excel2003 tu cherches la ligne 65536+1 ou la colonne IV décalée de 1 à droite.
Comme je n'ai aucune idée du tableau de départ, et que j'ai la flemme de comprendre ce que vaut la formule, je n'irais pas plus loin.
A+

 

[bond]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Autre diagnostic : Référence circulaire !

Intersection sur la zone $C$7:$G$7

commençant obligatoirement en C7 mais allant aléatoirement de la ligne C7 à IV7

$A$7:$G$7

 

[Adrien]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Rebonjour,

Mais l'ennui, c'est que cette formule m'a été donnée sur ce forum même, et que question formules matricielles, je ne m'y connais pas. Alors en VBA en plus ...

Adrien

 

[Adrien]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Up,

Je n'arrive toujours pas à comprendre ce que je dois faire et où je dois le faire.

Cette formule fonctionne pourtant lorsque je l'inscris dans une cellule. Et en plus, elle vient d'ici. J'ai essayé l'enregistrement de macro qui n'a pas l'air de me donner de code valable.

Adrien

 

[phlaurent55]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Bonjour à tous,

Up,

Je n'arrive toujours pas à comprendre ce que je dois faire et où je dois le faire.

Cette formule fonctionne pourtant lorsque je l'inscris dans une cellule. Et en plus, elle vient d'ici. J'ai essayé l'enregistrement de macro qui n'a pas l'air de me donner de code valable.

Adrien

je pense que la meilleure solution serait de mettre le fichier en pièce jointe et d'expliquer de manière complète, claire et précise le fonctionnement de ton fichier
voir une construction de formule en VB, c'est pas toujours évident à comprendre
sans le fichier c'est ........

à+
Philippe

 

[Adrien]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Rebonsoir,

Voici un fichier que j'espère assez clair, car, je l'avoue, pour donner des exemples, je suis assez chaotique comme le prouve un de mes anciens posts

Adrien

 

[phlaurent55]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Re,

tu nous parles d'erreur de code et tu mets en pièce jointe un fichier .xlsx sans code

ton fichier en retour
j'ai bouleversé un peu ton agencement ............ mais c'est pour le confort ...... et sans code

à+
Philippe

 

[Adrien]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Bonjour,

C'est de nouveau moi. On ne peut pas dire que je ne cherche pas. Mais j'ai pourtant tout essayé ... ça fait plus de deux semaines que je planche sur ce code qui n'a décidément pas envie de se finir...

Je n'arrive toujours pas à insérer ma formule en VBA et j'ai essayé tout un tas de variantes, mais aucune n'a l'air de fonctionner. En menant des recherches, j'ai cru trouver la solution en voyant que l'insertion FormulaArray ne pouvait pas dépasser 225 caractères, jusqu'à ce que je comprenne que c'était valable pour 98 mais pas au dessus ...

Donc, je vous serais vraiment reconnaissant de m'accorder encore quelques instants afin de m'expliquer pourquoi ma formule ne peut pas être ajoutée en VBA.

Merci,

Adrien

 

[bond]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Ton fichier en l'état actuel serait bienvenue vu les nombreuses variantes présentes dans cette discussion.
Précise ton objectif : Quelle formule ? Quelle destination ?... enfin tout ce qui permettrai de comprendre ton projet pour ceux qui ne sont pas dedans... bref, explique comme si on avait 5 ans.

 

[Adrien]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

En fait, je n'arrive pas à insérer mon fichier qui est trop gros (oui, j'ai actuellement 80 pages et je ne compte pas m'y arrêter)

Mais je pense pouvoir être simple et clair, ainsi qu'arriver à me faire comprendre sans fichier. Enfin ... je vais essayer quoi ...

Étape 1: Les feuilles.

Il y en a une quarantaine qui comportent plus ou moins le même code.

Elles gèrent toutes de points (je crois qu'en France on dit côtes).

La seule chose qui diffère entre ces feuilles est le type de la matière (Maths, Français, ...)

Une feuille contient :

- 2 Colonnes de noms, prénoms et numéros

- Une série de colonnes d'interrogations contenant en ligne 1 le max, après, la note obtenue.

- Une colonne tout à la fin comportant le total ramené sur dix.

La formule utilisée :

En français, la formule de total est celle-ci :

=SI(ESTERREUR(SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$J$7)));DECALER(C8;;;1;NB($A$7:$J$7))))/SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$J$7)));DECALER($C$7;;;1;NB($A$7:$J$7))))*10);SI(A13="";"";"Absent");(SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$J$7)));DECALER(C8;;;1;NB($A$7:$J$7))))/SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$J$7)));DECALER($C$7;;;1;NB($A$7:$J$7))))*10))

Cette formule m'a été donnée sur ce forum, ce qui fait que je ne l'ai peut-être pas manipulée assez bien en VBA.

Quel est le problème ?

Eh bien j'ai voulu ajouter un tri des élèves par nom et prénom. Mais les cotes s'en retrouvaient bouleversées puisqu'il triait tout ... même les formules de total qui elles ne devaient pas changer de place ...

Donc, je me suis dit que j'allais utiliser le ("xlToLeft") pour atteindre la colonne de totaux. Mais une fois arrivé là, je n'arrive pas à insérer ma formule dans la cellule.

La formule est matricielle, j'ai donc utilisé ce code :

   Selection.FormulaArray = _
        "=IF(ISERROR(SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))),OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))))/SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))),OFFSET(R7C3,,,1,COUNT(R7C1:R7C7))))*10),IF(R[5]C[-3]="""","""",""Absent""),(SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))),OFFSET(RC[-1],,,1,COUNT(R7C1:R7C7))))/SUM(IF(ISNUMBER(OFFSET(RC[-1],,,1,COUNT(R7C1:" & _
        "OFFSET(R7C3,,,1,COUNT(R7C1:R7C7))))*10))"

Le code me donne l'erreur 1004.


Merci,

Adrien

 

[bond]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Franchement malgré tes explications, c'est difficile à visualiser...
Pourrais tu extraire de ton fichier une feuille qui contient le dessin d'entregistrement (libellés des colonnes, qqs enregistrements fantaisistes, et, indiques la destination de la formule que la macro doit poser.... et mets ce bout de fichier ici.

edit : ça serait bien aussi de comprendre comment tu définis le résultat de cette formule.

 

[Adrien]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Bonsoir,

Ce fichier se trouve dans mon message #4

Et le résultat de cette formule ? Une sorte de moyenne, mais une variante.

Adrien

 

[bond]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

y'a pas de fichier dans le #4, alors si c'est du #7 qu'il faut partir, confirme stp que la formule qu'il y a dedans est juste et que le résultat attendu est correct.

Dans le fichier ; la formule est

{=SI(ESTERREUR(SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$G$7)));DECALER(C8;;;1;NB($A$7:$G$7))))/SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$G$7)));DECALER($C$7;;;1;NB($A$7:$G$7))))*10);SI(A8="";"";"Absent");(SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$G$7)));DECALER(C8;;;1;NB($A$7:$G$7))))/SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$G$7)));DECALER($C$7;;;1;NB($A$7:$G$7))))*10))}

Dans ton #11, elle est indiquée :

=SI(ESTERREUR(SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$J$7)));DECALER(C8;;;1;NB($A$7:$J$7))))/SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$J$7)));DECALER($C$7;;;1;NB($A$7:$J$7))))*10);SI(A13="";"";"Absent");(SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$J$7)));DECALER(C8;;;1;NB($A$7:$J$7))))/SOMME(SI(ESTNUM(DECALER(C8;;;1;NB($A$7:$J$7)));DECALER($C$7;;;1;NB($A$7:$J$7))))*10))

Dans les 2 cas la plage couverte est étrange par rapport au fichier du #7, je m'attendrais à $A$7:$H$7 au lieu de $A$7:$G$7 ou $A$7:$J$7.
A te lire...

 

[Adrien]

Re : [VBA] Erreur d'exécution 1004 suite à l'ajout d'une formule par VBA.

Salut,

La formule est la même. Elle m'a été donnée par un membre du forum et elle permet de changer la plage à l'ajout/suppression de colonnes.

C'est une formule qui apparemment à l'air correcte, mais que je n'arrive pas à insérer en VBA.

Adrien