XL 2013 Ajouter des formules dans un tableau de dimensions variables

[Ananas94]

Bonjour,

Après maintes recherches sur le sujet (qui m'ont déjà pas mal aidées), je suis bloquée dans un programme.
En effet, 2 tableaux (construits et nommés) sont présents:
-le premier, nommé "Tab_Données_ORLI" recueille des données brutes recueillies dans un logiciel.
-Le second nommé "Tab_Calculs" effectue des calculs à partir de ces données.
Les dimensions de ces deux tableaux (en terme de nombre de lignes essentiellement) est variable à chaque utilisation.

Je souhaite effectuer les actions les suivantes :
1- Cliquer sur le bouton de la macro "calcul profil de puissance" -> OK
2- Une fois que j'ai cliqué sur le bouton, le second tableau doit se remplir avec des formules (celles de calculs) et s'arrêter à la dernière ligne remplie. -> NOK

Je suis bloquée en effet car :

1-J'ai un message d'erreur qui est le suivant :

Mon code est (l'erreur au débogage est en gras) :

Option Explicit
Sub Calcul_Profil_Puissance()

    Dim Jours_Stretch As Range, JEPP_stretch As Range, Puissance_corr As Range, Profil_theo As Range, Ecart_theo_exp As Range
    Dim LO1 As ListObject, LO2 As ListObject
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cell_d
    Dim Formule_1 As String, Formule_2 As String, Formule_3 As String, Formule_4 As String, Formule_5 As String

    Set ws1 = Sheets("Données_ORLI")
    Set ws2 = Sheets("Résultats")

    Set LO1 = ws1.ListObjects("Tab_Données_ORLI")
    Set LO2 = ws1.ListObjects("Tab_Calculs")
    
    Formule_1 = "=" & "B9-$B$9"
    Formule_2 = "=" & "I10+(H10-H9)*J10/100"
    Formule_3 = "=" & "SI(OU(C10 > 101, 5, C10 < 80), J9, C10)"
    Formule_4 = "=" & "100-0,0584*I9-0,0054*I9*I9+3*I9*I9*I9*0,00001"
    Formule_5 = "=" & "K9-J9"

    
With LO2 'Pour la formule 1'
        .ListColumns("Jours de stretch").Range(2, 1).FormulaLocal = Formule_1 'La formule 1 est placée dans la 1ère case de la colonne'
       [B] .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Select[/B]
        .ListColumns("Jours de stretch").Range(2, 1).Etirer_formules "H9:H" 'On étire la formule jusqu'à la fin du tableau, qui a des dimensions variables'
    End With
    
    With LO2 'Pour la formule 2'
        .ListColumns("JEPP de stretch").Range(3, 2).FormulaLocal = Formule_2 'La formule 1 est placée dans la 1ère case de la colonne'
        .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Select
        .ListColumns("JEPP de stretch").Range(3, 2).Etirer_formules "I10:I" 'On étire la formule jusqu'à la fin du tableau, qui a des dimensions variables'
    End With
    
    With LO2 'Pour la formule 3'
        .ListColumns("Puissance corrigée").Range(3, 3).FormulaLocal = Formule_3 'La formule 1 est placée dans la 1ère case de la colonne'
        .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Select
        .ListColumns("Puissance corrigée").Range(3, 3).Etirer_formules "J10:J" 'On étire la formule jusqu'à la fin du tableau, qui a des dimensions variables'
    End With
    
    With LO2 'Pour la formule 4'
        .ListColumns("Profil théorique NACRE").Range(2, 4).FormulaLocal = Formule_4 'La formule 1 est placée dans la 1ère case de la colonne'
        .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Select
        .ListColumns("Profil théorique NACRE").Range(2, 4).Etirer_formules "K9:K" 'On étire la formule jusqu'à la fin du tableau, qui a des dimensions variables'
    End With
    
    With LO2 'Pour la formule 5'
        .ListColumns("Ecart puissance théorie-exp").Range(2, 5).FormulaLocal = Formule_5 'La formule 1 est placée dans la 1ère case de la colonne'
        .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Select
        .ListColumns("Ecart puissance théorie-exp").Range(2, 5).Etirer_formules "L9:L" 'On étire la formule jusqu'à la fin du tableau, qui a des dimensions variables'
    End With
            
End Sub


Private Sub Etirer_formules(cell_d)
    Dim der_ligne As Long
    der_ligne = LO2.Cells(Rows.Count, 1).End(xlUp).Row 'Pour la colonne "Jours de stretch"
    Range(cell_d & der_ligne).FillDown
End Sub

2-Lorsque j'ai étiré la formule, des nombre négatifs apparaissent alors que les données corrélées sont vides. Le tableau est systématiquement entièrement rempli alors que ce n'est pas le cas.

Vous trouverez ci-joint le fichier.

Auriez-vous une idée pour me dire où est l'erreur s'il vous plaît ?
Je vous remercie vivement par avance,
Excellente journée,

Anna

 

[Staple1600]

Bonjour le fil, Anna

Comment est-ce possible?
Toujours la même réponse
L'inattention

Fais ce test sur une feuille vierge, et observe le contenu des formules

Sub Test()
Cells(2, 3).FormulaR1C1 = "=SI(OU(C10>101,C10<80),J9,C10)"
Cells(3, 3).Formula = "=SI(OU(C10>101,C10<80),J9,C10)"
Cells(4, 3).FormulaLocal = "=SI(OU(C10>101;C10<80);J9;C10)"
Cells(5, 3).Formula = "=IF(OR(C10>101,C10<80),J9,C10)"
Cells(6, 3).FormulaR1C1 = "=IF(OR(R[7]C>101,R[7]C<80),R[6]C[7],R[7]C)"
End Sub

Sinon, étant pour un usage normal d'un tableau structuré, je m’abstiendrai de commentaire sur tout dévoiement illogique de la chose

 

[Ananas94]

Bonjour,

Merci beaucoup pour ton aide! En fait, l'erreur provenait simplement du fait qu'il était écrit FormulaR1C1 (qui comprend les formules en anglais) au lieu de FormulaLocal (qui comprend les formules en français).
Ainsi, j'ai mis : .Cells(2, 3).FormulaLocal = "=SI(OU(C10>101,5;C10<80);J9;C10)"
et ça fonctionne !
Donc si j'ai bien compris :
FormulaLocal : pour les formules dans ma langue (ici français)
Formula : Par défaut, en anglais, avec colonnes identifiées avec des lettres
FormulaR1C1 : en anglais, avec les colonnes numérotées à la place des lettres



J'ai un autre problème ensuite : je souhaite tracer des graphiques liés à ces données, dont les plages sont variables.
Pour cela, je me suis renseignée sur ce forum, et j'ai appris l'existence de la fonction DECALER.
J'ai ainsi formaté mes plages de données pour tracer les graphes, et les fonctions DECALER pour chacun des axes.
Exemple pour le premier graphe dans l'onglet "R1_Puissance_Corrigee" :

Pour l'axe des abscisses Jours de stretch :

=DECALER(N5;Plage_Graphe_Puissance_corr[Jours de stretch];1;Plage_Graphe_Puissance_corr[Jours de stretch];1)

Pour l'axe des ordonnées "Puissance corrigée" :

=DECALER('R1_Puissance_corrigee'!$O$5;15;;NBVAL('R1_Puissance_corrigee'!$1:$1)-1)

En effet, la fonction DECALER se décompose comme suit:
Référence : d'où part la 1ère valeur ($O$5 ou $N$5)
Lignes : Nombre de lignes où la référence doit être décalée
Colonnes: Nombre de colonnes où la cellule de référence doit être décalée (toutes les valeurs sont sur une colonnes, donc on ne décale pas)
Hauteur: Hauteur, en nombre de lignes, attendue pour le résultat. C'est là que le nombre de lignes varie, donc j'ai placé NBVAL pour le nombre de valeurs.
Largeur: idem, avec le nombre de colonnes. Ici, il y a toujours une seule colonne.

Pour l'axe des ordonnées, j'ai testé avec la fonction NBVAL (puisque le nombre de données varie) et pour l'axe des abscisses, j'ai tenté de juste sélectionner la colonne entière.

Pour l'axe des ordonnées, j'obtiens #VALEUR! et pour l'axe des abscisses, j'obtiens #REF!... Ainsi, je ne sais pas trop quoi faire, je pense que les deux formule sont fausses, mais je ne comprends pas pourquoi.
Aurais-tu une idée ?
Je te remercie vivement,
Excellente journée!

 

[Staple1600]

Bonjour le fil, Anna

•>Anna
A chaque jour suffit sa peine
A chaque question suffit sa discussion
(Cf la charte du forum)
Si tu as une nouvelle question qui n'a rien a voir avec la discussion en cours, alors hop!, création d'un nouvelle discussion.

 

[Ananas94]

Ah oui pardon. Je le fais de suite.
Excellente journée