1. Ce site utilise des "témoins de connexion" (cookies) conformes aux textes de l'Union Européenne. Continuer à naviguer sur nos pages vaut acceptation de notre règlement en la matière. En savoir plus.

Tableaux dans vba

Discussion dans 'Forum Excel' démarrée par akni, 8 Mars 2017.

?

Tableau

  1. Choix du sondage...

  2. Choix du sondage...

Les résultats sont uniquement visibles après avoir voté.
  1. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Re bjr Dranreb,
    Je me suis inspiré du fichier GigogneAkni du poste 73, j'ai nommé l'onglet DT01 FVent1, J'ai pu créer les onglets par DR, par contre je ne retrouve pas des tableaux corrects par DR. Je pense que certains Next sont mal positionnés.

    Merci de ton aide

    KIM


    Sub Synthese_ParOP_DPT_Site_GrouperCol_vR312()
    'Synthese R3
    Dim C As Long, TS(), DCols As Dictionary, TSpl() As String, N As Long, L As Long, _
    OP As SsGr, TotOP(7 To 10) As Double, DR As SsGr, TotDR(7 To 10) As Double, SITE As SsGr, _
    Détail, Statut As String, Montant As Double, NbG(7 To 11) As Long, TotG(7 To 10)
    Dim F As Long, FDest As Worksheet, LMax As Long

    For F = FVent1.Index To ThisWorkbook.Worksheets.Count
    Set FDest = ThisWorkbook.Worksheets(F): FDest.Name = FDest.CodeName: Next F
    F = FVent1.Index - 1


    ReDim TS(1 To 1000, 1 To 10)
    For C = 1 To 10: TS(1, C) = Choose(C, "DPT", "OP", "SITE", "SUB", _
    "", "", "A+D+F", "B+C+G", "E", "Total"): Next C
    Set DCols = New Dictionary
    For C = 7 To 9: TSpl = Split(TS(1, C), "+")
    For N = 0 To UBound(TSpl): DCols(TSpl(N)) = C: Next N, C
    L = 1


    For Each DR In Gigogne(ColUti(FBase1.[A5:p5]), 1, 10, 2)
    L = 1
    With ThisWorkbook.Worksheets: If F = .Count Then .Item(F).Copy After:=.Item(F)
    F = F + 1: Set FDest = .Item(F): End With
    FDest.Name = DR.Id
    ' FDest.Name = "T_" & DR.Id

    For C = 7 To 10: TotDR(C) = 0: Next C

    For Each OP In DR.Co
    For C = 7 To 10: TotOP(C) = 0: Next C
    LMax = 0
    For Each SITE In OP.Co
    LMax = LMax + SITE.Count
    L = L + 1
    TS(L, 1) = DR.Id
    TS(L, 2) = OP.Id
    TS(L, 3) = SITE.Id
    For Each Détail In SITE.Co
    Statut = Détail(16): Montant = Détail(14)
    If Not DCols.Exists(Statut) Then MsgBox "Statut """ & Statut & """ non prévu.", vbCritical: Exit Sub
    C = DCols(Statut)
    TS(L, C) = TS(L, C) + Montant: TS(L, 10) = TS(L, 10) + Montant
    NbG(C) = NbG(C) + 1: NbG(10) = NbG(10) + 1: Next Détail
    For C = 7 To 10: TotOP(C) = TotOP(C) + TS(L, C): Next C, SITE


    L = L + 1: TS(L, 2) = "Total " + DR.Id + " - " + OP.Id

    For C = 7 To 10: TS(L, C) = TotOP(C): TotDR(C) = TotDR(C) + TotOP(C): Next C
    L = L + 1: Next OP


    L = L + 1: TS(L, 1) = "Total " + DR.Id
    For C = 7 To 10: TS(L, C) = TotDR(C): TotG(C) = TotG(C) + TotDR(C): Next C
    L = L + 1

    L = L + 1: TS(L, 1) = "Total"
    For C = 7 To 10
    TS(L, C) = TotG(C)
    TS(L + 1, C) = NbG(C)
    Next C
    Next DR

    FDest.Rows(5).Resize(1000000).ClearContents
    FDest.[A5].Resize(L, 10) = TS

    End Sub
     

    Pièces jointes:

  2. Dranreb

    Dranreb XLDnaute Barbatruc

    Inscrit depuis le :
    31 Janvier 2011
    Messages :
    11940
    "J'aime" reçus :
    663
    Sexe :
    Masculin
    Habite à:
    Belfort
    Utilise:
    Excel 2016 (PC)
    Oui, le vidage du tableau doit être fait juste avant Next DR, non plus tout à la fin.
    Conseil: évite d'ajouter des lignes vides inutiles dans le code: ça ne facilite ni l'établissement ni le suivi d'une indentation correcte.
     
  3. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Bonsoir Dranreb,
    Résultat OK, j'ai supprimé les lignes vides par contre j'ai étét obligé de Redim le tableau TS après For Each DR In Gigogne(ColUti(FBase1.[A5:p5]), 1, 10, 2).
    1- Comment formater les lignes de Col 1 à col 10 dont je retrouve Total en Col1 ou en Col2 en fond Jaune et gras ?
    2-Peut-on optimiser le code ci-dessous ?

    Merci encore
    KIM


    Sub Synthese_ParOP_DPT_Site_GrouperCol_vR312()
    'Synthese R3
    Dim C As Long, TS(), DCols As Dictionary, TSpl() As String, N As Long, L As Long, _
    OP As SsGr, TotOP(7 To 10) As Double, DR As SsGr, TotDR(7 To 10) As Double, SITE As SsGr, _
    Détail, Statut As String, Montant As Double, NbG(7 To 11) As Long, TotG(7 To 10)
    Dim F As Long, FDest As Worksheet, LMax As Long

    For F = FVent1.Index To ThisWorkbook.Worksheets.Count
    Set FDest = ThisWorkbook.Worksheets(F): FDest.Name = FDest.CodeName: Next F
    F = FVent1.Index - 1

    ReDim TS(1 To 1000, 1 To 10)
    For C = 1 To 10: TS(1, C) = Choose(C, "DPT", "OP", "SITE", "SUB", _
    "", "", "A+D+F", "B+C+G", "E", "Total"): Next C
    Set DCols = New Dictionary
    For C = 7 To 9: TSpl = Split(TS(1, C), "+")
    For N = 0 To UBound(TSpl): DCols(TSpl(N)) = C: Next N, C
    ' L = 1

    For Each DR In Gigogne(ColUti(FBase1.[A5:p5]), 1, 10, 2)
    With ThisWorkbook.Worksheets: If F = .Count Then .Item(F).Copy After:=.Item(F)
    F = F + 1: Set FDest = .Item(F): End With
    FDest.Name = DR.Id

    ReDim TS(1 To 1000, 1 To 10)
    L = 1
    For C = 1 To 10: TS(1, C) = Choose(C, "DPT", "OP", "SITE", "SUB", _
    "", "", "A+D+F", "B+C+G", "E", "Total"): Next C

    For C = 7 To 10: TotDR(C) = 0: Next C

    For Each OP In DR.Co
    For C = 7 To 10: TotOP(C) = 0: Next C
    LMax = 0
    For Each SITE In OP.Co
    LMax = LMax + SITE.Count
    L = L + 1
    TS(L, 1) = DR.Id
    TS(L, 2) = OP.Id
    TS(L, 3) = SITE.Id
    For Each Détail In SITE.Co
    Statut = Détail(16): Montant = Détail(14)
    If Not DCols.Exists(Statut) Then MsgBox "Statut """ & Statut & """ non prévu.", vbCritical: Exit Sub
    C = DCols(Statut)
    TS(L, C) = TS(L, C) + Montant: TS(L, 10) = TS(L, 10) + Montant
    NbG(C) = NbG(C) + 1: NbG(10) = NbG(10) + 1: Next Détail
    For C = 7 To 10: TotOP(C) = TotOP(C) + TS(L, C): Next C, SITE

    L = L + 1: TS(L, 2) = "Total " + DR.Id + " - " + OP.Id

    For C = 7 To 10: TS(L, C) = TotOP(C): TotDR(C) = TotDR(C) + TotOP(C): Next C, OP

    L = L + 1: TS(L, 1) = "Total " + DR.Id
    For C = 7 To 10: TS(L, C) = TotDR(C): TotG(C) = TotG(C) + TotDR(C): Next C

    L = L + 1: TS(L, 1) = "Nbre Total"
    For C = 7 To 10: TS(L, C) = NbG(C): Next C

    FDest.Rows(4).Resize(1000000).ClearContents
    FDest.[A4].Resize(1000, 10) = TS
    For C = 7 To 10: TotG(C) = 0: NbG(C) = 0: Next C
    Next DR

    End Sub
     

    Pièces jointes:

  4. Dranreb

    Dranreb XLDnaute Barbatruc

    Inscrit depuis le :
    31 Janvier 2011
    Messages :
    11940
    "J'aime" reçus :
    663
    Sexe :
    Masculin
    Habite à:
    Belfort
    Utilise:
    Excel 2016 (PC)
    Pour chaque DR on peut mettre les police et fond normaux et à chaque fois qu'on écrit des totaux dans TS(L, … changer les formats de FDest.Rows(L + 3)
    Il n'y a me semble t-il rien à optimiser. Mais c'est sûr qu'un ReDim TS(1 To 1, 1 To 10) suffirait au début, juste pour la fabrication du DCols.
     
  5. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Bonjour Dranreb et le forum,
    Pour changer les formats en effet FDest.Rows(L + 3) fonctionne mais pourquoi L+3 ?
    Pour finaliser ce tableau de synthèse, comment je peux copier, pour chaque DR, sous le tableau de Synthèse de la DR, les données source de chaque DR à partir de FBase1 (onglet Base2) ?
    Merci
    KIM
     
    Dernière édition: 19 Avril 2017
  6. Dranreb

    Dranreb XLDnaute Barbatruc

    Inscrit depuis le :
    31 Janvier 2011
    Messages :
    11940
    "J'aime" reçus :
    663
    Sexe :
    Masculin
    Habite à:
    Belfort
    Utilise:
    Excel 2016 (PC)
    Bonjour
    Parce que comme vous écrivez TS à partir de A4, la ligne 1 du tableau va à la 4 de la feuille. L + 3.
    Pas compris la seconde question.
     
  7. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Re bonjour,
    Je n'avais percuté pour le décalage du début du tableau. Merci.
    Les données de toutes les DR sont dans un onglet Base2, de nom FBase1 défini par
    For Each DR In Gigogne(ColUti(FBase1.[A5 P5]), 1, 10, 2)
    En créant dans un onglet par DR un tableau de synthèse pour la DR, je souhaite recopier sous ce tableau les données brutes de la DR contenues dans FBase1.
    voir onglet DT01
    Comment faire ?
    Merci
    KIM
     

    Pièces jointes:

  8. Dranreb

    Dranreb XLDnaute Barbatruc

    Inscrit depuis le :
    31 Janvier 2011
    Messages :
    11940
    "J'aime" reçus :
    663
    Sexe :
    Masculin
    Habite à:
    Belfort
    Utilise:
    Excel 2016 (PC)
    Ça ne me parait vraiment pas souhaitable de modifier la feuille de données en y ajoutant des choses derrière. Comme il n'y a aucune plage mise sous forme de tableau Excel nulle part, on se base sur tout ce qu'il y a, alors des choses ajoutées derrière en feraient ensuite partie.
     
  9. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Re,
    Peut-etre ma demande n'est pas claire. Je ne souhaite pas modifier la feuille de données. Je voudrais recopier, de la feuille des données, les données de chaque DR dans son onglet, au dessous du tableau de synthèse qui vient d'être créé pour avoir un onglet complet par DR : La synthèse et les données concernées.
    Au lancement de la macro, l'onglet de chaque DR est effacé (ClearContents ou Delete)
    Merci encore

    KIM
     
  10. Dranreb

    Dranreb XLDnaute Barbatruc

    Inscrit depuis le :
    31 Janvier 2011
    Messages :
    11940
    "J'aime" reçus :
    663
    Sexe :
    Masculin
    Habite à:
    Belfort
    Utilise:
    Excel 2016 (PC)
    Ah… Il est possible de réserver un tableau supplémentaire dans lequel on ajoute 1 à sa ligne Ld (d comme détail) à chaque Détail dont on y copie tout par une boucle sur C. Il ne resterait qu'à le verser en FDest.Cells(L + Quelque chose, "A").Resize(Ld, 16), L étant toujours le numéro de ligne jusqu'où on est arrivé dans TS.
     
    Dernière édition: 19 Avril 2017
  11. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Merci c'est OK. Ce tableau recap est terminé. Je me suis débarrassé des formules.
    J'utilise des formules pour calculer l'âge d'un bâtiment et créer un tableau de synthèse et calculer la surface pondérée selon l'âge du bâtiment.
    Je regardera et reviendrai vers toi. Merci pour ta disponibilité.
    Bonne fin de journée
    KIM
     
  12. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Bonjour Dranreb,
    Je reviens vers toi concernant le même tableau de synthèse.
    Est-il possible de saisir une formule dans un tableau TS() ?
    J'ai une fonction qui me remplit dans une colonne le libellé de chaque DT
    =CherchApprox(A5;l_Cdpt;l_Ldpt)
    Je la rajoute manuellement après avoir créé la synthèse par DT.
    Dans la boucle ci-dessous,
    For Each DR In Gigogne(ColUti(FBase1.[A5:p5]), 1, 10, 2)
    ......
    For Each OP In DR.Co
    .......
    For Each SITE In OP.Co
    L = L + 1
    TS(L, 1) = DR.Id
    TS(L, 2) = OP.Id
    TS(L, 3) = SITE.Id
    est-il possible de rajouter :
    TS(L,5) = "=CherchApprox(DR.Id;l_Cdpt;l_Ldpt)"
    et si oui quelle est la bonne syntaxe ?
    Merci
    KIM
     
  13. Dranreb

    Dranreb XLDnaute Barbatruc

    Inscrit depuis le :
    31 Janvier 2011
    Messages :
    11940
    "J'aime" reçus :
    663
    Sexe :
    Masculin
    Habite à:
    Belfort
    Utilise:
    Excel 2016 (PC)
    Bonjour.
    Porquoi faudrait il que ce soit une formule ?
    TS(L, 5) = CherchApprox(DR.Id, l_Cdpt, l_Ldpt) ça ne fonctionnerait pas ?
     
  14. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Merci, dans ma tête je pensais aux formules. En effet c'est une fonction personnalisée.
    Est-ce qu'on peut utiliser des noms définis dans le gestionnaire de noms ? et si oui comment?
    Car l_Cdpt et l_Ldpt sont des noms de plage définis dans le gestionnaire de noms.
    Il me sort une erreur :
    upload_2017-4-21_12-27-18.png

    KIM
     
  15. Dranreb

    Dranreb XLDnaute Barbatruc

    Inscrit depuis le :
    31 Janvier 2011
    Messages :
    11940
    "J'aime" reçus :
    663
    Sexe :
    Masculin
    Habite à:
    Belfort
    Utilise:
    Excel 2016 (PC)
    À essayer:
    Code (Visual Basic):
    TS(L, 5) = CherchApprox(DR.Id, [l_Cdpt], [l_Ldpt])
    sous toutes réserves.
     
  16. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Ce format fonctionne. Merci
    Bon we
    KIM
     
  17. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Bonsoir Dranreb,
    Je viens de constater une anomalie dans mes tableaux de synthèse. Je retrouve une colonne vide à 0 dans DCols.
    La colonne de référence pour les titres du tableau de synthèse n'a pas de cellule vide (filtre sur la colonne):
    upload_2017-4-22_22-29-0.png
    La ligne :
    Set DCols = DicInvent(Données, ColTitre, ColDép:=7): CFin = DCols.Count + 7
    me donne une colonne supplémentaire vide :
    Dans la fenêtre des variables DCols a 8 valeurs dont la dernière est vide :
    upload_2017-4-22_22-33-34.png

    As-tu une idée pourquoi ?
    Merci d'avance
    KIM
     
  18. Dranreb

    Dranreb XLDnaute Barbatruc

    Inscrit depuis le :
    31 Janvier 2011
    Messages :
    11940
    "J'aime" reçus :
    663
    Sexe :
    Masculin
    Habite à:
    Belfort
    Utilise:
    Excel 2016 (PC)
    Bonsoir.
    Comme ça, non, je ne peux pas voir.
    Je ne peux que recommander de vérifier Données.
     
  19. KIM

    KIM XLDnaute Accro

    Inscrit depuis le :
    7 Avril 2005
    Messages :
    1068
    "J'aime" reçus :
    1
    Utilise:
    Excel XP (PC)
    Bonsoir Dranreb, et le forum
    Avec la ligne :
    Set DCols = DicInvent(Données, ColTitre, ColDép:=7): CFin = DCols.Count + 7
    me donne 8 valeurs pour DCols dont la dernière est vide.
    Dans le tableau R32, je retrouve une colonne Col N vide.
    Ce tableaest créé par la macro "Synthese_ParOP_DPT_Site_GrouperCol_vR32"
    J'ai contrôlé mes données mais je n'ai trouvé aucune anomalie.
    Merci pour votre aide.
    KIM
     

    Pièces jointes:

  20. Dranreb

    Dranreb XLDnaute Barbatruc

    Inscrit depuis le :
    31 Janvier 2011
    Messages :
    11940
    "J'aime" reçus :
    663
    Sexe :
    Masculin
    Habite à:
    Belfort
    Utilise:
    Excel 2016 (PC)
    Ça vient de :
    Set Données = FBase1.[A5:p5].Resize(FBase1.[A64000].End(xlUp).Row - 1)
    Supposons que la End(xlUp).Row soit à 1000. - 1 ça fait 999, depuis 5 pour 999 va jusqu'à 1003 : trois lignes vide de trop.
    Pourquoi ne pas faire plutôt
    Set Données = ColUti(FBase1.[A5:p5])
    Il ne se trompe jamais, lui.
     
    Dernière édition: 24 Avril 2017

Partager cette page