VBA - Erreur d'exécution 1004 avec la formule "Forecast"

Bonjour,

Je débute avec VBA depuis environ 3 semaines et je n'ai aucune expérience de codage avant ça. Ce forum m'a déjà bien aidé à résoudre certaines difficultés, mais je bute aujourd'hui sur une erreur que je n'arrive pas à résoudre dans ma macro.

Je travaille sur une prédiction à court terme de la croissance et de la consommation d'animaux au sein d'un élevage, à partir de mesures quotidiennes issues d'un automate. Le modèle que j'utilise à besoin d'être initialisé à partir d'une première croissance (qui donne un ordre d'idée du potentiel de chaque animal et de sa tendance de croissance initiale). Je récupère donc les pesées quotidiennes de chaque animal pendant 7 jours, sans effectuer de calcul. Au 7ème jour, j'applique ensuite une soustraction entre deux prévisions successives du poids sur ces 7 derniers jours (formule Forecast, ou prévision sous Excel), afin d'avoir le différentiel moyen entre 2 jours (voir code). Je préfère ça plutôt que de faire la différence entre la moyenne J1 - J6 et la moyenne J2-J7, moins précise.

Pourtant, à certaines exécutions de ma macro, je bute sur une erreur 1004. Si je répète l'opération plusieurs fois, sur les mêmes données, l'erreur n’apparaît pas forcément. J'ai vérifié mon code et je ne comprends pas d'où pourrait provenir l'erreur, donc si quelqu'un à une idée...

Je préfère ne pas joindre le fichier concerné, compte tenu de sa taille et de la taille des macros, et aussi par ce que j'aimerais ne pas diffuser les informations qu'il contient.

Sub Test1()

Dim N1 As Integer
Dim yr, xr As Range

For N1 = 2 To NbAnimal + 1

    Sheets("PoidsBrut").Range(Cells(3, N1), Cells(9, N1)).Interior.ColorIndex = 6
    Set yr = Sheets("PoidsBrut").Range(Cells(3, N1), Cells(9, N1))
    Set xr = Sheets("PoidsBrut").Range(Cells(3, 1), Cells(9, 1))
    Sheets("PoidsBrut").Cells(10, N1) = Application.WorksheetFunction.Forecast(Sheets("PoidsBrut").Cells(4, 1), yr, xr) - Application.WorksheetFunction.Forecast(Sheets("PoidsBrut").Cells(3, 1), yr, xr)

Next

End Sub

La feuille "PoidsBrut" contient en colonne le numéro attribué à chaque animal (en ligne 2), et en ligne les dates par ordre croissant (lignes 3 à 9). Ce tableau est donc complété par la pesée de chaque animal pour chaque jour. Le format des données est bon, et il n'y a pas de valeurs manquantes.

Bonjour

Trouver une erreur 1004 sans le fichier ....

Cordialement

Bonjour,

Une erreur 1004 est une erreur Excel ! Il est donc difficile de détecter l'origine sans aller voir dans Excel tout le contexte d'exécution...

Compte-tenu de la fonction utilisée, je te conseillerais d'aller tester tes cas litigieux en feuille de calcul, ce qui te permettra de voir si tu obtiens une erreur dans les mêmes cas : si c'est cela, tu auras mis le doigt sur ton erreur 1004 et tu auras une idée plus précise de sa cause selon l'erreur renvoyée par Excel (#VALEUR!, #N/A, ou #DIV/0!).

Cordialement.

NB- Ta variable yr, non typée est de type Variant, et non Range (une variable se type individuellement).

Salut,

Voici une formulation plus rigoureuse de ton code :

Sub Test1()
Dim NbAnimal As Integer
Dim N1 As Integer
Dim yr As Range, xr As Range

NbAnimal = 2

With Sheets("PoidsBrut")
    Set xr = .Range(.Cells(3, 1), .Cells(9, 1))
    For N1 = 2 To NbAnimal + 1
        Set yr = .Range(.Cells(3, N1), .Cells(9, N1))
        yr.Interior.ColorIndex = 6
        .Cells(10, N1) = Application.WorksheetFunction.Forecast(.Cells(4, 1), yr, xr) - Application.WorksheetFunction.Forecast(.Cells(3, 1), yr, xr)
    Next

End With
End Sub

Ce code sera-t-il plus robuste ?

A+

H

Merci pour vos remarques, effectivement, je n'avais pas la bonne syntaxe pour la déclaration de mes variables (que j'ai donc déclarées individuellement).

LordNelson45, Je n'ai pas précisé que le "NbAnimal" est déclaré dans la "vraie" macro, puisqu'il ne s'agit ici que d'un fragment que j'ai isolé pour le tester séparément.

MFerrand, j'ai testé quelques cas directement sous Excel, sans que la formule ne renvoie d'erreur.

Compte tenu de vos remarques, j'ajoute également un fichier plus complet avec de quoi faire tourner la macro concernée. Ne soyez pas trop dur, elle n'est pas encore terminée, et je suis conscient qu'il est possible de la simplifier ou de la formuler différemment !

Merci !

Pedro,

Dans le code que je t'ai passé, ce n'est NbAnimal l'élément important, c'est l'utilisation de WITH ... END WITH

car :

Set yr = .Range(.Cells(3, N1), .Cells(9, N1))

est différent de

Set yr = Sheets("PoidsBrut").Range(Cells(3, N1), Cells(9, N1))

si la feuille "PoidsBrut" n'est pas active.

A+

H

Je crois que je saisie la nuance, et c'est vrai que j'utilise rarement "With... End With" dans les codes que j'ai fait jusqu'ici. J'en prends bonne note pour éviter de faire des erreurs à l'avenir et rendre le code plus lisible.

Mais dans la macro qui contenait le fragment que j'ai posté, la cellule de destination du résultat est différente de la source (feuille "Lissage" pour le résultat, "PoidsBrut" pour la source).

Là encore, tu considères que c'est seulement une question de lisibilité alors que c'est précisément là que se situe l'erreur :

With Sheets("PoidsBrut")
    Set xr = .Range(.Cells(3, 1), .Cells(9, 1))

est correct alors que dans

Set xr = Sheets("PoidsBrut").Range(Cells(3, 1), Cells(9, 1))

Cells(3, 1) et Cells(9, 1) se réfèrent à la feuille active, d'où erreur si PoidsBrut n'est pas la feuille active !

Il faudrait écrire :

Set xr = Sheets("PoidsBrut").Range(Sheets("PoidsBrut").Cells(3, 1), Sheets("PoidsBrut").Cells(9, 1))

mais c'est plus sympa avec With.

Ok, c'est la question que j'allais te poser par rapport à la syntaxe, juste pour savoir comment faire dans le cas où l'on travail sur plusieurs feuille dans une même formule. Je retravaille mon code et je vérifie si ça permet de résoudre mon problème (sans doute que oui...). Merci de ton aide.

With... End With t'économise généralement une variable. Pour les autres objets qui interviennent à l'intérieur du bloc, utilise des variables objets...

NB- With... End With n'est pas qu'une simplification d'écriture, l'exécution en est plus rapide. Et de même avec des variables, VBA y accède plus rapidement.

J'ai revu mon code, il n'est pas encore parfait, mais cette erreur semble résolue ! Merci encore de votre aide, et sans doute à bientôt pour de nouveaux problèmes !