XL 2010 [Résolu] Index Equiv en VBA

[Spinzi]

Bonjour à tous,

je recherche à calculer une partie de mon tableau non pas à l'aide de formule mais via VBA.

Mon tableau (onglet "Synthèse") est composé d'une partie TCD et d'une autre partie manuelle (je n'utilise pas le TCD pour toutes mes données car j'ai bcp plus d'informations dans mon fichier original et le TCD est vraiment lent). Les formules et le TCD reposent sur l'onglet "Base".

Je souhaiterais, grace à un bouton, pouvoir lancer une macro qui me remonte (comme le fait actuellement ma formule index equiv) les informations de l'onglet "base" (sous forme de tableau excel) en fonction des ordres (lignes) et des entetes de colonnes de mon onglet synthèse.

Pourriez vous m'aiguiller ? Je suis nul en VBA.

Merci d'avance,
Spinzi

 

[Bebere]

bonjour
Spinzi
un exemple à mettre dans un module général

Public Sub Synthese()
    Dim TblSynthese(), TblOrdre, ListObj As ListObject, Lr As ListRow, Cel As Range
    Dim i As Long, j As Long, L As Long

    TblOrdre = Feuil2.[C8:C16]
    Set ListObj = Feuil1.ListObjects("Base")
    For i = 1 To UBound(TblOrdre)
        For Each Cel In ListObj.DataBodyRange.Columns(5).Cells
            If Cel.Value = TblOrdre(i, 1) Then
            L = L + 1
                Set Lr = ListObj.ListRows(L): j = j + 1
                ReDim Preserve TblSynthese(1 To 3, 1 To j)
                TblSynthese(1, j) = Lr.Range.Cells(1, 8)    'v1
                TblSynthese(2, j) = Lr.Range.Cells(1, 9)    'v2
                TblSynthese(3, j) = Lr.Range.Cells(1, 10)    'arc
                Exit For
            End If
        Next Cel
    Next i

    TblSynthese = Application.Transpose(TblSynthese)
    Feuil2.[F8].Resize(UBound(TblSynthese, 1), UBound(TblSynthese, 2)) = TblSynthese

End Sub

 

[Spinzi]

Bonjour Bebere !

Merci pour ta solution, ca fonctionne très bien dans l'exemple.
Est-ce que je peux optimiser mon besoin ?
Au lieu d'avoir des référence figée en termes de lignes (C8:C16) il faudrait que dynamiquement dans la macro, il aille regarder dans la colonne B le nombre de valeurs.
Pareil pour les entêtes de colonnes où j'aimerai pouvoir les modifier (par exemple si j'intervertis les colonnes V1 et V0, que la macro puisse indexer correctement).

je mets en copie un fichier avec une structure similaire à mon besoin.

J'ai essayé d'adapter le code VBA mais j'ai une erreur.

Je reviendrai car je dois également créer une autre macro pour la mise en forme du fichier (suppression des lignes avec écrit "Oui" à l'intérieur, masquer des colonnes avec "Oui" au dessus et qu'il m'ouvre automatiquement une fenêtre pop up pour enregistrer sous.).

Merci d'avance de ton aide,

Spinzi

 

[Bebere]

bonsoir
voilà un code mais tu devras changer les paramètres du tcd car il n'est pas possible de changer les données d'un tcd

Public Sub Synthese()
    Dim TblSynthese(), TblOrdre, ListObj As ListObject, Lr As ListRow, Cel As Range
    Dim i As Long, j As Long, L As Long, Cv0 As Byte, Cv1 As Byte, Cv2 As Byte, Carc As Byte
    Cv0 = Feuil3.Cells.Find("V0").Column: Cv1 = Feuil3.Cells.Find("V1").Column
    Cv2 = Feuil3.Cells.Find("V2").Column: Carc = Feuil3.Cells.Find("ARC").Column

    i = Application.CountIf(Feuil3.[D:D], "=" & "11X*")
    TblOrdre = Feuil3.Range("D16:D" & i + 15)
    Set ListObj = Feuil1.ListObjects("Base")
    For i = 1 To UBound(TblOrdre)
        For Each Cel In ListObj.DataBodyRange.Columns(5).Cells
            If Cel.Value = TblOrdre(i, 1) Then
                L = L + 1
                Set Lr = ListObj.ListRows(L): j = j + 1
                ReDim Preserve TblSynthese(1 To 4, 1 To j)
                TblSynthese(1, j) = Lr.Range.Cells(1, 7)    'v0
                TblSynthese(2, j) = Lr.Range.Cells(1, 8)    'v1
                TblSynthese(3, j) = Lr.Range.Cells(1, 9)    'v2
                TblSynthese(4, j) = Lr.Range.Cells(1, 10)    'arc
                Exit For
            End If
        Next Cel
    Next i

    TblSynthese = Application.Transpose(TblSynthese)
    For i = 1 To UBound(TblSynthese, 1)
    Feuil3.Cells(14 + i, Cv0) = TblSynthese(i, 1)
    Feuil3.Cells(14 + i, Cv1) = TblSynthese(i, 2)
    Feuil3.Cells(14 + i, Cv2) = TblSynthese(i, 3)
    Feuil3.Cells(14 + i, Carc) = TblSynthese(i, 4)
Next
End Sub

 

[Spinzi]

Bonjour,

merci pour ce code.
Cependant je n'arrive pas à saisir le fonctionnement de cette macro : il faut que je décrive une variable pour toutes les colonnes que je souhaite afficher ?

Car dans le fichier ci joint (que j'ai retravaillé), mon but est que, sur une plage définie, la macro fasse l'index equiv en se basant sur les numéro d'ordres et les entetes de colonnes.
Si je dois déclarer une variable pour chaque colonne à afficher, je perds en dynamisme ...

Donc mon besoin : avoir une fonction dynamique à partir des cellules G15 à Z15 puis de AF15 à AM15 et des ordres contenus à partir de la cellule D16.

Désolé si je ne suis pas clair, n'hésitez pas à revenir vers moi si il y a des choses que vous ne comprenez pas !

ps : je parle d'index equiv parce que je base ma recherche sur une ligne et un entete de colonne : mon besoin c'est que la fonction me ramène l'information, peu importe la fonction utilisée ...

 

[Bebere]

bonjour
Spinzi ce serait bien plus simple de faire une extraction avec le filtre élaboré
dans la feuille budget les entêtes dont tu as besoin ou un petit userform ou tu choisis les entêtes et le ou les critères
dis m'en un peu plus sur les critères

 

[Spinzi]

Bonjour,

désolé je mets du temps à répondre.
ma selection se fait sur :
_Un département comprend plusieurs centres de coûts
_Un centres de coûts comprend plusieurs ordres
_Un ordre a un responsable budgétaire

Ce fichier est utilisé lors des révisions budgétaires V0 V1 et V2.
La V2 est simulée en meme temps que la V0 de l'année d'après.

Pour faciliter la selection et garder la main sur le fichier (je ne souhaite pas de vba pour gérer les criteres de selection) j'utilise un TCD qui me ramène les données organisationnelles (ordre, libellé, centre de couts, etc.).

Ensuite, je fais un index equiv pour me ramener la donnée de l'ordre de la révision budgétaire (V1, V2, etc.).

Pour exemple dans le fichier ci joint, il faudrait que la partie verte soit calculée automatiquement en fonction du numéro d'ordre et de l’entête de colonne (je n'ai pas mis les informations relatives à la V1).

La partie rouge est a compléter manuellement par les différents services (donc rien à faire).

A votre disposition

Spinzi

 

[Spinzi]

Bonjour à tous,

je me permets de faire un petit up concernant mon souci.
J'insiste sur le fait d'avoir du VBA pour améliorer les performances de calcul.
J'ai essayé avec des formules personnalisées cependant elles utilisent des formules matricielles et derrière j'a ides macro pour cacher des lignes et les 2 ne font pas bon ménage donc KO.

Pour rappel :
le but est que, suivant la PJ dans mon message précédent (VBA IPv3), dans l'onglet "Budget", je puisse avoir de manière dynamique les informations correspondant aux ordres (colonne D) en fonction des différentes entête de colonnes (G15 à BH15).

Merci de votre aide, j'essaie de me mettre au VBA, mais il m'est impossible de pondre une macro optimisée ...

Spinzi

 

[Bebere]

bonjour
Spinzi
je regarde ce que je peux faire

 

[Spinzi]

Bonjour Bebere,

super merci bcp !

 

[Bebere]

bonjour
Spinzi
le code est fait pour V0 et V1,V1 à tester

 

[Spinzi]

Bonjour Bebere,

après premier test ça m'a l'air PARFAIT !
J'ai rajouté une ligne dans le TCD, votre macro l'a prise en compte et calculée, tout se met bien à jour, niquel.
EDIT : en fait l'intégration d'une nouvelle ligne n'a pas fonctionnée. Serait il possible de prendre en compte une nouvelle ligne ? la variable pourrait etre que la matrice ce calcule depuis la cellule F16 jusqu’à la prochaine cellule de la colonne "Ordres" vide (ligne 26 dans mon fichier).

Comme je ne suis pas très bon en macro, quels seraient les paramètres à modifier si pour l'implémenter dans mon fichier réel ?
_Nom de feuil à modifier ?
_Si je souhaite rajouter des colonnes qui devraient être calculée par la macro ?
_Si avoir la plage de calcul de la macro étendue dynamiquement suivant la présence d'un ordre ou non (voir mon EDIT, où faut il changer la variable qui détermine le nombre de lignes où se calcule la macro ?

Merci bcp pour votre travail =)

Spinzi

 

[Bebere]

bonjour
Spinzi si tu ajoutes des données dans base, il faut actualiser le tcd et exécuter le code
edit: il faut ajouter la ligne dans le tableau et la référence de la plage de base sera adaptée
dans le gestionnaire de noms
çà peut se faire par code

 

[Spinzi]

Bonjour,

Ok j'essaie d'adapter votre code à mes feuilles et je reviens vers vous si ça coince qqpart !

Encore merci =)

Spinzi

 

[Bebere]

Spinzi
un exemple pour faciliter les changements ,tu changes le nom des feuilles Ws et Ws1
le tableau et tcd sont mis à jour par code