XL 2010 Table structurée avec formules==> Tableau VBA

[vgendron]

Hello

Je viens vers vous pour un souci de faisabilité:
avec PJ
1) Sur ma feuille, j'ai une table structurée
Celle-ci a sa ligne, d'entetes, la ligne total (avec des formules) ET une colonne avec des formules (dernière colonne pour l'exemple, mais il y a en a plusieurs dans le fichier original)

2) dans mon code, je place TOUTE cette table dans un tableau VBA (TabData() as variant) afin de pouvoir effectuer un traitement sur les données
si je fais un habituel TabData=Table.range.value ==> les formules disparaissent et sont remplacées par leur résultat
par contre. si je fais un TabData=Table.range.formula ==> LA, les formules sont stockées "intactes" sous forme de string

je peux donc faire mon traitement : pour l'exemple: si le contenu n'est pas une formule, je fais *10 (formule = commence par "=")

3) le problème arrive ici: lorsque je souhaite coller le résultat du tableau vba dans la table structurée... Table.Range = TabData
les nouvelles valeurs calculées sont ok, MAIS les formules disparaissent..

donc la question: Comment coller le contenu du tableau vba dans la table en gardant les formules..?
c'est possible dans un sens.. pourquoi pas dans l'autre??

Merci

 

[sylvanu]

Bonjour Vgendron,
Je pense qu'un Array ne peut contenir que des valeurs, ni formules, ni mise en forme.
Donc dans un sens ça marche, mais dans l'autre non.

 

[Phil69970]

Bonjour Vincent et Sylvain

un Array ne peut contenir que des valeurs, ni formules, ni mise en forme.

Pourtant j'ai vu ceci

Range.FormulaArray, propriété (Excel)

Rubrique de référence sur Office VBA

learn.microsoft.com

Avec cet exemple :

Worksheets("Sheet1").Range("E1:E3").FormulaArray = "=Sum(A1:C3)"

@Phil69970

 

[fanch55]

Salut,
Code à valider:

Sub TestFormules()
Dim TabData() As Variant
Dim Table As ListObject

    Set Table = ActiveSheet.ListObjects(1)
   '.formula pour garder les formules sous leur forme
    TabData = Table.DataBodyRange.Formula

    For i = 1 To UBound(TabData, 1)
        For j = 1 To UBound(TabData, 2)
            If IsNumeric(TabData(i, j)) Then TabData(i, j) = TabData(i, j) * 10
        Next j
    Next i
    
    Table.DataBodyRange = TabData

End Sub

 

[TooFatBoy]

Bonjour,

Pourtant j'ai vu ceci

Salut Phil 😉
Je pense que quand le camarade sylvanu parlait d'un "array", il parlait en réalité d'un tableau en mémoire et non d'une plage de cellules sur une feuille.


vgendron : il semble étrange de travailler sur tout le TS s'il contient des formules, vu qu'elles sont là pour effectuer elles-mêmes des calculs.
Je suppose que ton but est de n'utiliser qu'une seule instruction plutôt que de travailler séparément sur plusieurs parties du TS. N'est-il pas ?

 

[vgendron]

Hello tous
merci pour vos retours
le code de @fanch55 est nickel !
ca garde les formules, et ca garde la forme "Table structurée" sur ma feuille

de ce que je vois de différent par rapport à mon code, c'est que tu ne charges QUE le databodyrange
alors que je chargeais le range entier
et à priori. il ne faut pas toucher aux entetes

 

[vgendron]

Je suppose que ton but est de n'utiliser qu'une seule instruction plutôt que de travailler séparément sur plusieurs parties du TS. N'est-il pas ?

@TooFatBoy , oui c'est ca

le databodyrange contient des colonnes editées par l'utilisateur, mais AUSSI des colonnes qui contiennent des formules
si je n'avais eu qu'une seule table, j'aurais effectivement traité chaque partie séparémment.. mais la...le nombre de tables est conséquent, sur plusieurs onglets...

!! Aïe !! .. en écrivant.. je suis en train de me rendre compte que, dans mon tableau vba.. je mets le contenu de plusieurs tables présentes sur la feuille...

Sauf à faire boucler le code de fanch55, je pense que je vais devoir Revoir la structure des feuilles pour n'avoir qu'une seule table....
bon bah. j'ai encore quelques neurones a faire chauffer... 🙂

 

[vgendron]

bon. j'ai trouvé la solution. du moins.. une solution
==>restituer la table cellule par cellule...sur l'exemple, ca reste rapide... à voir au final..

 

[fanch55]

bon. j'ai trouvé la solution. du moins.. une solution
==>restituer la table cellule par cellule...sur l'exemple, ca reste rapide... à voir au final..

Je ne pense pas que ce soit la solution la plus adaptée, elle est trop adhérente à l'existant .
Si jamais les "entre-tables" sont modifiés avec des zones numériques, il faudra tout reprendre.

Il vaut mieux boucler sur tous les listobjects, voire toutes les tables du classeur,
avec cette méthode chaque table peut avoir sa propre structure ...

Sub Lob_Book() ' on traite toutes les feuilles du classeur
Dim Sh As Worksheet
    For Each Sh In ThisWorkbook.Worksheets
        Lob_Sheet Sh
    Next
End Sub
Sub Lob_Sheet(Optional Sh As Worksheet) ' on traite toutes les tables de la feuille
Dim Lob As ListObject
    If Sh Is Nothing Then Set Sh = ActiveSheet
    Application.ScreenUpdating = True
    For Each Lob In Sh.ListObjects
        Traite_Table Lob
    Next
End Sub
Sub Traite_Table(Table As ListObject)
Dim TabData() As Variant
   '.formula pour garder les formules sous leur forme
    TabData = Table.DataBodyRange.Formula

    For i = 1 To UBound(TabData, 1)
        For j = 1 To UBound(TabData, 2)
            If IsNumeric(TabData(i, j)) Then TabData(i, j) = TabData(i, j) * 10
        Next j
    Next i
    
    Table.DataBodyRange = TabData

End Sub

 

[vgendron]

effectivement, j'ai été embêté avec les entre tables. que j'ai géré en testant le contenu de la première colonne
normalement, l'entre table est toujours vide (dans l'application concernée). mais.. normalement ca commence par N et pourtant.. c'est un P 😂😂