Erreur 1004 définie par l'application ou par l'objet

Bonjour,

Le but de mon code est de déclencher le calcul de valeurs intermédiaires (sortes de moyennes) quand une cellule est initialisée ou modifiée dans le feuille.

Sub Worksheet_Change(ByVal Target As Range)
    Dim x As Integer
    Dim y As Integer
    x = Target.Row
    y = Target.Column
    If Not Intersect(Target, Range("A1:G100")) Is Nothing Then
    'avant de faire la moyenne, on regarde s'il y a une ou deux données manquantes
        If Cells(x, y - 2).Value = "" Then
            V1 = Cells(x, y - 3).Value
            V2 = Target.Value
            Cells(x, y - 2).Value = (V2 - V1) / 3 + V1
            V3 = Cells(x, y - 2).Value
            Cells(x, y - 1).Value = V3 + (V2 - V1) / 3
        Else
            V1 = Cells(x, y - 2).Value
            V2 = Target.Value
            Cells(x, y - 1).Value = (V2 - V1) / 2 + V1
        End If
    End If
End Sub

J'ai un problème avec la ligne :

        If Cells(x, y - 2).Value = "" Then

Le programme plante systématiquement au niveau de cette ligne et affiche "Erreur d'exécution 1004. Erreur définie par l'application ou par l'objet". Pourtant, il me semble que les objets x et y sont définis correctement, et j'ai vérifié, ils ont bien une valeur attribuée au moment où le programme s'arrête. J'ai également vérifié dans un code indépendant plus simple si la ligne If marchait bien, toujours avec x et y définis selon la Target. Elle fonctionne, donc a priori le problème ne vient pas du contenu de la ligne.

En cherchant sur les forums, on comprend vite que cette erreur 1004 peut provenir de plein de sources différentes (au moins en apparence). Des personnes avaient eu cette erreur lorsqu'il y avait un problème dans l'emplacement du code. J'ai donc vérifié en lançant le programme dans "Feuil1" ou dans "Module 1" : dans la feuille, c'est là que le code plante, et dans le module, il ne se passe strictement rien quand une cellule est modifiée/initialisée, ce que je ne comprend pas (en effet je n'ai pas bien saisi la différence entre les deux emplacements). Mais ce test ne m'aiguille pas plus sur l'origine de l'erreur.

Est-ce que vous auriez des pistes à me conseiller ?

Merci beaucoup de votre aide :)

EGal

Bonjour,

C'est "normal" que vous ayez ce genre d'erreur :
1. Les subs dans des Worksheets sont de type Private
2. Comme vous modifiez des valeurs qui déclanchent d'autres modifications de valeurs, alors ça redéclanche la macro, jusqu'à l'erreur. Pour ça Application.EnableEvents est votre ami
3. Quand y vaut 0 ou 1 ça va, mais quand il vaut 2, c'est quoi la colonne 0 ? Et lorsqu'il vaut 3, que va donner Cells(x, y - 3) ? Colonne 0 également.

Il faut que vous anticipiez tout ces cas, soit par vous même, soit en nous expliquant exactement qu'est ce que vous souhaitez faire, si telle cellule de telle colonne change alors on doit faire ce calcul, sinon on doit faire ça, etc etc ...

En attendant, un code qui ne génère pas d'erreur dès lors qu'on modifie des valeurs de la colonne D uniquement (car sinon on se retrouve à chercher des valeurs dans la colonne 0 ou -1 > Erreur). Je vous ai ajouté quelques lignes de commentaires (non exaustives) afin que vous compreniez les erreurs. Dès lors que vous avez un y- quelque chose, il faut s'assurer que cette valeur ne donne pas une valeur inférieure ou égale a 0 :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
Dim y As Integer
Application.EnableEvents = False
x = Target.Row
y = Target.Column
If Not Intersect(Target, Range("A1:G100")) Is Nothing Then
'avant de faire la moyenne, on regarde s'il y a une ou deux données manquantes
    If Cells(x, y - 2).Value = "" Then 'Va générer une erreur si y<3
        Cells(x, y - 2).Value = WorksheetFunction.Average(Cells(x, y - 3), Target.Value) 'Va générer une erreur si y<4
        V1 = Cells(x, y - 3).Value 'Va générer une erreur si y<4
        V2 = Target.Value
        Cells(x, y - 2).Value = (V2 - V1) / 3 + V1 'Va générer une erreur si y<3
        V3 = Cells(x, y - 2).Value 'Va générer une erreur si y<3
        Cells(x, y - 1).Value = V3 + (V2 - V1) / 3 'Va générer une erreur si y<2
    Else
        V1 = Cells(x, y - 2).Value
        V2 = Target.Value
        Cells(x, y - 1).Value = (V2 - V1) / 2 + V1
    End If
End If
Application.EnableEvents = True
End Sub

Dans l'attente de votre retour extrêmement détaillé sur ce que vous souhaitez réaliser si vous en avez besoin.

Cdlt,

Bonjour, merci beaucoup pour votre aide ! C'était clairement l'énorme faille que je n'avais pas vu... Application.EnableEvents a tout réglé !

Pour ce qui est des valeurs de y, j'avais pensé à ce problème mais j'attendais d'avoir au moins un programme qui tourne pour m'en occuper après. Je ne modifiais que des cellules avec des grands x et y pour tester le programme donc je savais que l'erreur ne venait pas de là.

Bonne continuation,

EGal