Tableau structuré => VBA - transformé formule avec réf. style A1 en adresse tableau style [@NomdEntete]

Bonjour,
j'aimerai savoir si il existe un moyen (ou une manière de procéder) pour transformer des formules (dont les réf. sont en style A1)
contenu dans un tableau structuré, en adresse tableau style [@NomdEntete] voir plutôt comme ceci NomTableau[@NomdEntete]

La condition de cette transformation doit tenir compte que les adresses en question se trouve bien à l’intérieur du tableau structuré
et non à l’extérieur de ledit tableau, à moins que celle-ci pointe dans un autre tableau structuré;
dans ce cas il faudra transformé l'adresse en précisant en plus le nom du tableau comme ceci : NomTableau[@NomdEntete].

Exemple basique : =C2 + E2 deviendrait =[@Entete3]+[@Entete5] ou NomTableau1[@Entete3]+NomTableau1[@Entete5]

Merci par avance pour l'aide

Ryu

Salut ;)

Tu veux additionner des entêtes? C'est exprès que tu as pris des références structurées d'entête?

Hi,

Ca peut être additionné, multiplié, concaténé … etc
Et oui c'est bien les références structurées d'entêtes que j'aimerais à la place des références style A1 du moment que celles-ci font bien partie de la plage du tableau

Additionner des cellules d'entête pose problème car Excel les transforme automatiquement en texte :aie:

Pour le reste, je n'ai pas compris la finalité. Tu peux illustrer avec un exemple?

Cela étant dit, c'est "probablement" possible, mais c'est à mon avis vachement balaise. Repérer dans une formule ce qui est une adresse de cellule, dan le même tableau ou dans un autre tableau du classeur demande d'abord de parser la formule pour en extraire les adresses (relatives, mixtes ou absolues), et déjà là, ca va être vachement coton. Après, il faudrait repérer si elles font partie d'un tableau, si elles sont sur la même ligne, dans quelle colonne...

Peut-être Est-ce envisageable dans des cas très simples de formules (A2+C2) mais au delà, ça me semble assez casse-gu***

Citation:

---

Cela étant dit, c'est "probablement" possible, mais c'est à mon avis vachement balaise.

---

C'est bien pour cela que je pause la question … malheureusement il n'y a pas de FormulaTableau

je vais voir ce qui est possible et si vous avez des idée je suis preneur …

Question bête: Pourquoi veux-tu transformer des références classiques en tableau? Perso, à part pour les lire, je ne vois pas trop l'intérêt...

Bonsoir,

Il n'y a, comme le souligne Pierre, aucun intérêt à vouloir transposer des formules, qui, peu importe comment est leur syntaxe, fonctionnent.

Sauf si, Ryu est resté sur une discussion récente à propos des tableaux structurés et de la transposition de colonnes (avec formules...).
Et encore...

Bon, sinon, face à ton problème du jour, Ryu, il ne s'agit que d'une "très compliquée" transformation de chaîne de caractères...

Analyse donc un peu le résultat donné par les différentes propriétés de ta cellule :

Code:

---

1 2 3 4 5

ActiveCell.Formula ActiveCell.FormulaArray ActiveCell.FormulaLocal ActiveCell.FormulaR1C1 ActiveCell.FormulaR1C1Local

---

Personnellement, pour le type de formule que tu décris dans ton exemple, je me suis arrêté sur la dernière ActiveCell.FormulaR1C1Local.
Que nous donne cette propriété, avec la formule : "=C2+E2", formule placée en F2 :

Citation:

---

=LC(-3)+LC(-1)

---

Je penses, qu'à partir de cette formule, tu peux te débrouiller pour construire la chaîne nécessaire correspondante à ton tableau structuré.
Après tout :

1. Dans un tableau structuré, les formules sont identiques de la première à la dernière ligne, donc, n'est à modifier que la formule de la cellule sous l'entête de la colonne concernée,
2. On peut facilement connaître l'entête d'une colonne correspondante à C(-5) ou C(+18),
3. Peut-être plus galère (je n'y connais que dalle en tableau structuré) pour remplacer une expression comme L(-15)...

Après cela, tu devras procéder par étapes, de formules en formules, pour par exemple, transposer la formule (située en E16) : =SOMME.SI(B1:B30;"A";A1:A30)
qui te donne, via la propriété FormulaR1C1Local : "=SOMME.SI(L(-15)C(-3):L(14)C(-3);"A";L(-15)C(-4):L(14)C(-4))"

Je rejoins Pierre dans l'idée que cela semble très compliqué pour un résultat que l'on ignore, mais ... ça doit être faisable.

Bon courage.

Bon week-end à vous deux et à vos familles!

bonsoir ryu
j'ai pas tres bien compris mais une address (nom de colonne dans la formule est rendu en address en lecture) je viens tester

exemple en h8 j'ai "=([Prénom])"

Code:

---

1 2 3

Sub text() MsgBox Cells(8, "H").Precedents.Address End Sub

---

le message me donne l'address de la cellule en colonne "prénom" sur la meme ligne

regarde coté precedents sait-on jamais

Coucou Franck et Patrick,

Merci du petit coup de pouce, je viens tout juste de voir vos messages; j'étais en train de bosser sur le sujet et je pense (j'ai pas fini) avoir un début de solution
en plein test avec MsBox pour me diriger vers mon idée

En général les formules sont étendues à la colonnes, donc l'idée et de parcourir la 1ère ligne du DataBodyRange et de vérifier si il y a une formule (donc oui on a pensé à ma même chose Franck ;))
si oui comme, le suggère Patrick (J'y étais aussi - tu me l'as fait découvrir et je ne l'ai pas oublié), on utilise Precedents dans un for each pour avoir chaque adresse que l'on compare avec la formule afin de vérifier que celle-ci y soit bien
et ensuite on remplace l'adresse par l'adresse de l'en-tête - bien sur il y a les adresses relatives et absolues cf. Precedents, mais ce n'est qu'un petite goutte d'eau, j'ai déjà les idées bien en places;
une fois la formule remplacé on l'étends à la colonne :

voilà mon esquisse de vérification afin de créer le code pour reconstruire les formules :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Sub test()   For Each ListObj In ActiveSheet.ListObjects MsgBox ListObj.Name For Each LRow In ListObj.ListRows(1).Range If LRow.HasFormula = True Then MsgBox LRow.Formula 'MsgBox LRow.Precedents.Address For Each P In LRow.Precedents MsgBox P.Address(0, 0) & " | " & LRow.Formula Next End If Next Next   End Sub

---

C'est là où j'en étais avant de vous répondre (je n'ai pas encore fais la comparaison) - je continue

PS :Il me parait logique d'utiliser les adresses Tableaux au lieu des adresses en style A1, sinon quel est l'intérêt à MS de les avoir créé - c'est en gros, ou ca l'est, le principe des champs nommés)
En plus il est assez pratique de pointé dessus …

Bon week-end à vous aussi et à vos familles!

et Merci encore

Ryu

Edit : Corrections de texte

Bonjour,

attention à la limitation que .Precedents n'est fourni que si l’antécédent est sur la même feuille.
On peut donc avoir .HasFormula = True sans Precedents => plantage
Le tester avant le For Each.
Ca résoudra par la même occasion les formules du type =nom*3
Si nom est une constante, on n'aura pas d'antécédent non plus.
eric

Bonsoir Eric,

Merci pour l'info, je m'en suis aperçu 8O :aie:
je suis en train de regarder aussi du coté de NavigateArrow

Edit : sur les 1er test que je viens de faire avec NavigateArrow, ca à l'air de le faire - wait and see …

Bonjour,

Voilà où j'en suis :

---

Résultat Sans les Options à True
Feuil2
Tableau2
-------------------------
-------------------------
="Client"&Feuil1!B2 - $A$2
="Client"&Feuil1!B2

-------------------------
=D2&" "&E2 - $C$2
=[@Nom]&" "&[@Prenom]

-------------------------
=F2*G2 - $H$2
=[@Chiffre]*[@Pourcentage]


-------------------------
=F2+H2+I2 - $J$2
=[@Chiffre]+[@Resultat]+[@Ajout]
-------------------------

---

Résultat avec la fonction ChangeFollowArrowAdr en Option True
Feuil2
Tableau2
-------------------------
-------------------------
="Client"&Feuil1!B2 - $A$2
="Client"&Tableau1[@ID]

-------------------------
=D2&" "&E2 - $C$2
=Tableau2[@Nom]&" "&Tableau2[@Prenom]

-------------------------
=F2*G2 - $H$2
=Tableau2[@Chiffre]*Tableau2[@Pourcentage]

-------------------------
=F2+H2+I2 - $J$2
=Tableau2[@Chiffre]+Tableau2[@Resultat]+Tableau2[@Ajout]
-------------------------

---

Résultat avec les fonctions ChangeFollowArrowAdr et AdrTab en Option True
Feuil2
Tableau2
-------------------------
-------------------------
="Client"&Feuil1!B2 - $A$2
="Client"&Tableau1[@ID]

-------------------------
=D2&" "&E2 - $C$2
=[@Nom]&" "&[@Prenom]

-------------------------
=F2*G2 - $H$2
=[@Chiffre]*[@Pourcentage]

-------------------------
=F2+H2+I2 - $J$2
=[@Chiffre]+[@Resultat]+[@Ajout]

-------------------------

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

Sub CheckFromulaInListObj() Dim Sh_Name As String, Nom_Tab$, LRow As Range, Formule$, Coll As New Collection   With ActiveSheet   Sh_Name = .Name: Debug.Print Sh_Name   For Each ListObj In .ListObjects   Nom_Tab = ListObj.Name: Debug.Print Nom_Tab & vbCrLf; String(25, "-") & vbCrLf; String(25, "-")   For Each LRow In ListObj.ListRows(1).Range   If LRow.HasFormula = True Then Formule = LRow.Formula: Debug.Print LRow.Formula & " - " & LRow.Address   Set Coll = ChangeFollowArrowAdr(Sh_Name, LRow, Nom_Tab)   Debug.Print AdrTab(Sh_Name, Nom_Tab, Formule, LRow, Coll, True) & vbCrLf; String(25, "-") End If   Next Next End With Set Cel = Nothing End Sub

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

Function ChangeFollowArrowAdr(ws_Name As String, ByRef LRow As Range, Tab_Name, Optional FT As Boolean) As Collection Dim Coll As New Collection, NewSh_Name, Adr$   With LRow .ShowPrecedents: i = 1   Do With .NavigateArrow(True, i) NewSh_Name = ActiveSheet.Name: Adr = .Address(0, 0) If NewSh_Name <> ws_Name Then Adr = NewSh_Name & "!" & Adr   If FT = True And Not Sheets(NewSh_Name).Range(Adr).ListObject Is Nothing Then _ Tbl = Sheets(NewSh_Name).Range(Adr).ListObject.Name Else Tbl = ""   Coll.Add Tbl & "|" & NewSh_Name & "|" & Adr End With i = i + 1: Adr = .NavigateArrow(True, i).Address(0, 0): NewSh_Name = ActiveSheet.Name Loop While NewSh_Name & "!" & Adr <> ws_Name & "!" & LRow.Address(0, 0)   'MsgBox "On a fait le tour - On est revenu sur l'adresse " & LRow.Address End With Sheets(ws_Name).ClearArrows Set ChangeFollowArrowAdr = Coll   End Function

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Function AdrTab(Sh_Name$, Nom_Tab$, Formule$, LRow As Range, Coll As Collection, Optional FT As Boolean) As String Dim Entete$ For Each C In Coll Param = Split(C, "|"): T = Param(0): F = Param(1): A = Param(2) If T = "" And F <> Sh_Name Then Formule = Formule Else If FT And T = Nom_Tab Then Tbl = "" Else If F = Sh_Name And T = "" Then T = Nom_Tab Else Tbl = T Entete = Sheets(F).ListObjects(T).HeaderRowRange(Range(A).Column) Adr_EnteteTab = Tbl & "[@" & Entete & "]" Formule = Replace(Formule, A, Adr_EnteteTab) End If Next AdrTab = Formule End Function

---

PS : il doit y avoir qq. erreurs/améliorations de codage à faire - car je n'ai pas tout vérifier/tout scruter - ce n'est pas encore fini mais les 1er résultats on l'air pas mal
Peut être revoir aussi le nommage des variables, mais bon vu à l'heure ou j'ai fini cette partie …

J'attends vos retours svp - merci d'avance ;)

Ryu

Edit :
Pour remplacer par les formules tableaux :

Code:

---

LRow.Formula = AdrTab(Sh_Name, Nom_Tab, Formule, LRow, Coll)

---

à mettre après la ligne 19 du code de CheckFromulaInListObj

re
Bonjour ryu
je sais pas trop ce que tu me demande dans ton mp mais voila
Pièce jointe 412779
je suis bon quand le tableau commence en colonne1
tu dois donc ajouter ce decalage en fonction de la colonne 1 du tableau
par contre pour avoir testé la formule avec les noms , elle ne fonctionne pas bien entendu ;)

Salut.

Si vous échangez par mp, alors, merci de clore la présente discussion et de continuer en mp. Ce n'est pas très intéressant pour les autres intervenants s'ils n'ont pas tous les échanges, et ça les empêche de participer ;)

Bonjour Pierre,

On n'échange pas par MP j'ai juste demandé à Patrick si il pouvait venir sur la discussion pour tester et pas plus !!!!
Aucun code ou échange technique par MP !!!!

Patrick,

Citation:

---

par contre pour avoir testé la formule avec les noms , elle ne fonctionne pas bien entendu

---

Qu'entends-tu par la ? Peux tu me donner un exemple stp.
Parles-tu des champs nommés ?

re
oui si je remplace la formule initiale par par la formule avec les noms ca ne fonctionne pas d'ailleurs quand je la colle dans la cellule j'ai le msgbox d'aide de formule
par contre apres avoir essayer a la main comme ca

la formule A2*B2 tu peut l'ecrire simplement comme ca

Citation:

---

=([chiffre]*[pourcentage])(TESTE!!!)

---

quand je le fait a la main directement dans le listobject
ca me marque comme formule

Citation:

---

=Tableau1[[#Cette ligne];[chiffre]]*Tableau1[[#Cette ligne];[pourcentage]]

---

comme quoi vba excel est large

Autrement dit les arguments nom et ligne sont implicites en interne ce que je t'ai deja fait remarqué
je regarderais tout a l'heure en rentrant ca doit pas etre compliqué de remplacer les address de precedents cells OU !!cells(1) et cells(2) d'un area par les noms de colonnes

Re Patrick,

Après avoir testé, j'ai trouvé d'où vient le problème.
Cela se passe dans la fonction "AdrTab" , on peut sûrement régler le problème avec un Index Equive sur l'en-tête de tableau

tu decale tout tes indexs

index= index + range("nondetontableau").column-1
et pourquoi pas avec une variable
dim decal
decal= range("nondetontableau").column-1

tes index = index + decal