XL 2013 Ajouter des formules dans un tableau de dimensions variables

Ananas94

XLDnaute Junior
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 :
1586185235753.png


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

VB:
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
 

Pièces jointes

  • Profil stretch PMOX.xlsm
    69.1 KB · Affichages: 22
Solution
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
VB:
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 ;)

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Ananas,
Si l'idée est de remplir la matrice de formules, une proposition en PJ qui me semble plus simple.
Une formule que je n'ai pas compris :
VB:
SI(OU(C10 > 101, 5, C10 < 80), J9, C10)
Je ne vois pas ce que le 5 veut dire.
J'ai rectifié par :
Code:
SI(OU(C10 > 101,C10 < 80), J9, C10)
C'est à dire si C10 est compris entre 80 et 101 on met J9 sinon C10.
 

Pièces jointes

  • Profil stretch PMOX.xlsm
    87.4 KB · Affichages: 6

Staple1600

XLDnaute Barbatruc
Bonjour le fil, Anna, sylvanu

•>Anna
Donc en fait, tu ne tiens pas compte des précédents conseils
(ou simplification de tes codes)...:rolleyes:
(cf les précédentes discussions où je t'ai dépanné)

Je laisse donc sylvanu prendre le relais ;)
 

Ananas94

XLDnaute Junior
Bonjour Sylvanu et Staple1600 !
Merci beaucoup pour vos réponses, je suis ravie :) vous pouvez tous les deux m'aider en fait :) :) :)

-Sylvanu : merci pour votre code, vous avez réussi à faire tout ce que je voulais faire en 5 lignes, alors que je le faisais en une page entière (avec une faute !). Mon erreur est de ne pas avoir mémorisé que, comme disait Staple1600, il n'y a pas besoin d'étirer les formules dans un tableau, elles s'étirent toutes seules (ce qui me pose un problème aujourd'hui d'ailleurs !).

*Macro Calcul profil de puissance :
Je l'ai modifié un petit peu :

VB:
Option Explicit
Sub Calcul_Profil_Puissance()

   [I] 'Déclaration des variables'[/I]
    Dim plage As Long
    Dim nb_lignes As Integer
    
   [I] 'Comptage du nombre de lignes remplies du tableau "Tab_Données_ORLI"'[/I]
    nb_lignes = [Tab_Données_ORLI].Rows.Count
    
    [I]'Insertion des formules et redimensionnement dans le tableau "Tab_Calculs" :[/I]
    With [Tab_Calculs]
        .ClearContents
        .Cells(1, 5) = "=K9-J9"
        .Cells(1, 4) = "=100-0.0584*I9-0.0054*I9*I9+3*I9*I9*I9*0.00001"
        .Cells(1, 3).FormulaR1C1 = "=SI(OU(C10>101,C10<80),J10,C10)"
        .Cells(1, 2) = "=I10+(H10-H9)*J10/100"
        .Cells(1, 1) = "=B9-$B$9"
       [B] .Offset(1, 0).Resize Range(nb_lignes, Columns.Count)[/B] [I]'On ne garde que le nombre de lignes remplies du tableau "Tab_Données_ORLI" '[/I]
    End With
    
End Sub

Et j'obtiens le message d'erreur suivant :
1586246135917.png

Avec en débogage la ligne en gras surlignée en jaune. Le problème viendrait donc de Range... Je souhaiterais que le tableau "Tab_Calculs" ait autant de lignes remplies que celles du tableau "Tab_Données_ORLI". En effet, je ne souhaite pas que le tableau soit systématiquement rempli de formules fausses (car reliées à des cases vides) jusqu'au bout.
Je ne comprends pas où est l'erreur.. auriez-vous une idée ?

*Macro initialisation :

J'ai créé cette macro pour initialiser les 2 tableaux ; ce qui sera utile de faire avant chaque entrée de données brutes (plusieurs personnes de mon équipe vont utiliser cet outil). Par ailleurs, lorsque je dis que je souhaite réinitialiser les 2 tableaux , ceci signifie que les 2 tableaux doivent avoir initialement 820 lignes chacun. Pour ce faire, j'ai créé le code ci-dessous :

Sub Initialisation()
'Action de réinitialisation des 2 tableaux.'

Dim LO1 As ListObject, LO2 As ListObject
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Données_ORLI")
Set LO1 = ws1.ListObjects("Tab_Données_ORLI")
Set LO2 = ws1.ListObjects("Tab_Calculs")

'Effacement du contenu des deux tableaux
Range("Tab_Calculs").ClearContents
Range("Tab_Données_ORLI").ClearContents

'Redimensionnement des 2 tableaux :
LO1.Offset(1, 0).Resize(820, Columns.Count).Add(AlwaysInsert:=True).ListRows 'Le premier tableau a initialement 820 lignes
LO2.Offset(1, 0).Resize(820, Columns.Count).Add(AlwaysInsert:=True).ListRows 'Le second tableau a initialement 820 lignes

End Sub

Et lorsque je teste la macro "Initialisation", je tombe sur ce message :
1586246639256.png

avec la ligne en gras surlignée en jaune lors du débogage.

Le problème vient certainement de Add mais je ne vois vraiment pas où est l'erreur.. Auriez-vous une idée ?

Merci beaucoup :)

-Staple1600 : (quel est votre prénom en fait ?) Merci beaucoup pour votre message, et pour votre aide! Et d'ailleurs, je ne sais pas si vous avez remarqué, mais je trouve que mes codes ont tout de même progressé en cohérence par rapport à mes débuts laborieux... grâce à vous !

Excellente journée ! Et encore merci !

Anna
 

Pièces jointes

  • Profil stretch PMOX_V2.xlsm
    48.5 KB · Affichages: 7

Staple1600

XLDnaute Barbatruc
Bonjour le fil, Anna, Sylvanu

>Anna
Je parlais de ceci
Dans tes précédentes discussions, j'ai simplifié certaines de tes syntaxes.
Exemple
Ton code
VB:
Dim LO1 As ListObject, LO2 As ListObject
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Données_ORLI")
Set LO1 = ws1.ListObjects("Tab_Données_ORLI")
Set LO2 = ws1.ListObjects("Tab_Calculs"
Ma suggestion
Enrichi (BBcode):
Dim LO1 As ListObject, LO2 As ListObject
Set LO1 = Sheets("Données_ORLI").ListObjects("Tab_Données_ORLI")
Set LO2 = Sheets("Résultats").ListObjects("Tab_Calculs")
Mais au final, tu reprends ta syntaxe initiale dans tes nouvelles versions.
Ce qui fait que je dois remodifier N fois le code...
Ce qui devient vite chronophage ;)

D'où le passage de relais à sylvanu.

PS: Mon prénom c'est: l'agrafe ;-)
 
Dernière édition:

Ananas94

XLDnaute Junior
Bonjour Staple1600 (ou l'agraphe, comme vous préférez! j'adore l'effet de flou sur le nom !!)
Je ne sais pas si vous vous rappelez, mais c'est vous-même qui m'aviez suggéré cette écriture (que je trouve très bien).
Pour revenir à mon problème, je pense qu'en fait le soucis viendrait de Resize, car il est toujours dans mes lignes qui ont un problème .. trouvez-vous ma syntaxe correcte ?
merci
Anna
NB: A défaut de connaître votre vrai prénom, puis-je vous tutoyer ? :)
 

Staple1600

XLDnaute Barbatruc
Re

•>Anna
Relis mon message précédent attentivement.
Ce que je t'ai suggéré tu ne l'utilises pas (ou plus) !
(cf ton code dans ton message#1)
et celui (ma simplification) du message#5)
Tu vois bien que tu as rajouté du code, là ou moins j'en supprime ;)

PS: Pour paraphraser EXCEL: =SI(Staple_Tutoies=VRAI;"Tu";"Vous") ;)
Quant à mon prénom, si j'étais moi, je finirai par savoir qu'il se trouve quelque part dans les N discussions auxquelles j'ai participé depuis 15 ans.
 

Staple1600

XLDnaute Barbatruc
Re

•>Anna
Si j'étais moi, je lancerai cette macro
(dans n'importe quel classeur)
VB:
Sub Error_Horreur_Harar_Arthur()
Dim r As Range
Set r = Cells(1).Offset(1, 0).Resize(820, Columns.Count)
MsgBox r.Address & Chr(13) & "La plage contient: " & Format(r.Count, "#,##0") & " cellules.", 48, "Un peu grand comme tableau, non!"
End Sub
Puis je me pincerai ;)
 

Ananas94

XLDnaute Junior
Re

Je viens de modifier le code, selon ton test (ton texte ne s'affiche pas entièrement, mais peu importe, je suis quand même impressionnée !).
l'erreur vient donc de columns.count !! L'ordinateur ne peut pas redimensionner un tableau avec un nombre "infini" de colonnes. En fait, je souhaitais exprimer ici le fait que l'ordinateur ne change pas le nombre de colonnes, et qu'il y en ait autant qu'il en compte actuellement ..

Bref. Du coup je poursuis mon code avec ton aide. Je souhaite en fait à chaque réinitialisation (et calcul de puissance à fortiori) redimensionner le tableau. En effet, des lignes de formules vides (donc fausses) apparaissent dans l'espace restant du tableau et cela me dérange.

Ainsi, le code devient :

*Macro calcul profil de puissance :

VB:
Option Explicit
Sub Calcul_Profil_Puissance()

    'Déclaration des variables'
    Dim plage As Long
    Dim nb_lignes As Integer
    Dim r As Range
    
    'Comptage du nombre de lignes remplies du tableau "Tab_Données_ORLI"'
    nb_lignes = [Tab_Données_ORLI].Rows.Count
    
    'Insertion des formules et redimensionnement dans le tableau "Tab_Calculs" :
    With [Tab_Calculs]
        .ClearContents
        .Cells(1, 5) = "=K9-J9"
        .Cells(1, 4) = "=100-0.0584*I9-0.0054*I9*I9+3*I9*I9*I9*0.00001"
        .Cells(1, 3).FormulaR1C1 = "=SI(OU(C10>101,C10<80),J10,C10)"
        .Cells(1, 2) = "=I10+(H10-H9)*J10/100"
        .Cells(1, 1) = "=B9-$B$9"
        Set r = .Cells(1, 1).Offset(1, 0).Resize(nb_lignes, Columns.Count) 'essai
        '.Offset(1, 0).Resize Range(nb_lignes, Columns.Count) 'On ne garde que le nombre de lignes remplies du tableau "Tab_Données_ORLI" '
    End With
    
End Sub

L'erreur : "Propriété ou méthode non gérée par cet objet" :
.r.Add(AlwaysInsert:=True).ListRows

r ici exprime la plage de données que je souhaite ajouter au tableau. ainsi, dans la ligne .r.Add(AlwaysInsert:=True).ListRows, je dis que j'ajoute au tableau ce nombre de lignes.

Après j'ai ajouté ce code pour exprimer le fait que je veux ajouter 820 lignes (la plage r) à partir de la première ligne (pour avoir de base un tableau qui ait 820 lignes et qui après a une dimension adaptée :
'Set p_ligne = .End(x1Down).Row 'Première ligne à lauqelle on raccroche la plage
'.Rows(p_ligne).r.Add(AlwaysInsert:=True).ListRows 'Ajout de la plage à partir de la première ligne

mais ça ne fonctionne pas non plus, car soit-disant la variable x1Down n'est pas déclarée (mais ce n'est pas une variable !)


ou ici :

*Macro initialisation:
Code:
Option Explicit
Sub Initialisation()
'Action de réinitialisation des 2 tableaux.'

    Dim LO1 As ListObject, LO2 As ListObject
    Dim r As Range
  
    Set LO1 = Sheets("Données_ORLI").ListObjects("Tab_Données_ORLI")
    Set LO2 = Sheets("Données_ORLI").ListObjects("Tab_Calculs")
    
    'Effacement du contenu des deux tableaux
    Range("Tab_Calculs").ClearContents
    Range("Tab_Données_ORLI").ClearContents
    
    'Redimensionnement des 2 tableaux :
    
With LO1
        Set r = .Cells(1, 1).Offset(1, 0).Resize(820, 2) 'Détermination de la plage de cellules à ajouter
        .r.Add(AlwaysInsert:=True).ListRows 'On ajoute cette plage au tableau LO1
    End With
           
End Sub

L'erreur : "Propriété ou méthode non gérée par cet objet"
Avec surlignée : Set r = .Cells(1, 1).Offset(1, 0).Resize(820, 2)

Ici, je dis que r est la plage qui est à ajouter sous les tableaux pour les initialiser. On part de la cellule (1,1) du tableau, on décale d'une ligne à cause des en-têtes, et on redimensionne avec 820 lignes et le nombre identique de colonnes.
Je ne comprends pas où est l'erreur.


J'avoue que je suis un peu désespérée :(
Merci par avance pour ton aide Arthur ;)

Anna
 

Ananas94

XLDnaute Junior
Merci beaucoup pour votre réponse Sylvanu !

Je lis votre code avec attention et je ne comprends pas tout :

Sub Calcul_Profil_Puissance()
Application.ScreenUpdating = False
'Déclaration des variables'
Dim plage As Long
Dim nb_lignes As Integer
'Comptage du nombre de lignes remplies du tableau "Tab_Données_ORLI"'
nb_lignes = Application.CountA([Tab_Données_ORLI])
'Insertion des formules et redimensionnement dans le tableau "Tab_Calculs" :
With [Tab_Calculs]
.ClearContents
.Cells(1, 5) = "=K9-J9"
.Cells(1, 4) = "=100-0.0584*I9-0.0054*I9*I9+3*I9*I9*I9*0.00001"
.Cells(1, 3).FormulaR1C1 = "=SI(OU(C10>101,C10<80),J10,C10)"
.Cells(1, 2) = "=I10+(H10-H9)*J10/100"
.Cells(1, 1) = "=B9-$B$9"
End With
[Tab_Calculs].Range(Cells(1 + nb_lignes, 1), Cells(10000, 5)).ClearContents *
End Sub

*-> Pourquoi est-ce hors de la boucle With ? Par ailleurs, si j'ai bien compris cette ligne de code, on supprime ici les lignes qui partent de la dernière cellule de la 1ère colonne, et qui se terminent à la cellule n°10000 en dernière colonne.
En fait, les dimensions du tableaux peuvent tellement varier que supprimer des lignes "en trop" est risqué si d'une fois à l'autre, un nombre de ligne "petit" est entré. en effet, on va alors supprimer des lignes, alors qu'il faudra en ajouter. Je ne sais pas si je suis claire...

En définitive, je souhaite que :
*Macro calcul profil puissance :
- le nombre de lignes du tableau 2 (Tab_Calculs) soit égal à celui du tableau 1(Data_ORLI)
*Macro initialisation:
- le nombre de lignes de chacun de mes tableaux à chaque initialisation soit égal à 820. Pas plus, pas moins.

Je vous remercie,
Excellente journée,
Anna
 

Discussions similaires

Réponses
2
Affichages
378

Statistiques des forums

Discussions
311 733
Messages
2 082 011
Membres
101 866
dernier inscrit
XFPRO