XL 2016 Remplir automatiquement un tableau d'après des conditions et des valeurs

Cotriana · 2 Août 2022

Bonjour,

Mon problème est simple mais pourtant la macro à trouver me paraît compliqué.

Le problème est que j'ai automatisé tout le process entre les deux fichiers, l'un permet de saisir des dates de débuts en production, l'autre permet de communiquer ces dates au magasin à travers leur planning.

Cependant, avec ma macro, certains OF apparaissent mais pas tous alors je ne comprends pas. Peut-être que vous pouvez m'aider à comprendre pourquoi, ou bien me proposer un process plus simple.

Ci-dessous, l'extrait du fichier permettant aux gestionnaires de saisir les dates de débuts des OF.

1) Une fois les dates saisies (donc l'ordonnancement effectué), les gestionnaires doivent mettre à jour le planning du magasin pour qu'ils préparent les OF dont ils ont besoin.

2) La macro vient copier les colonnes OF-Réf-Date de début du premier fichier (synthèse OF) et les coller dans la feuille DATA_MNT du second fichier.

3) Ensuite, la macro vient déduire 7 jours à la date saisies (correspondant au délai de préparation par le magasin). Puis un TCD est créée permettant d'alimenter la feuille "Planning pour MN".

4) Ensuite un filtre est fait pour garder que les lignes non-vides, les copier, et les coller dans la feuille "Planning MNT" afin que le magasin récupère essentiellement les données utiles (donc les lignes non-vides)

PROBLEME: à l'étape 3, l'ensemble des données sont bien extraites du fichier des OF et bien renseignées dans le TCD, cependant, elle ne se mettent pas tous en place dans le tableau avec les formules matricielles.
(exemple: la référence 245310, ce n'est pas la seule.)

J'aimerai que le tableau "Planning pour MN" prennent TOUT en compte...

Je met également ci-dessous la macro réalisé mais je ne comprend pas pourquoi le tableau ne prend pas tout les OF. Il prend en compte que certaines références.

VB:

Sub PLANNING_MAGA()
'
' PLANNING_MAGA Macro
' Macro automatisant le planning du magasin

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False

'Ouvrir le planning maga Flux 1&2, créer une feuille, coller les données, créer le TCD (RAJOUTER OUVERTURE DU FICHIER!!!)
    'Workbooks.Open Filename:= _
        "file:///I:\400_LTS\09_BI\070_IND\05_ChargeCapa\Fichiers annexes\Montage Neuf\Ordo\(NE_PAS_MODIFIER)_PLANNING_MNT_Flux 1&2.xlsm", UpdateLinks:=3
    
    
    Workbooks("(NE_PAS_MODIFIER)_PLANNING_MNT_Flux 1&2.xlsm").Activate
    Sheets("Data_MNT_1").Visible = True
    Sheets("Data_MNT_1").Activate
    
    Application.Calculation = xlCalculationAutomatic
    
    Cells.Select
    Selection.Delete Shift:=xlUp
    
    Range("A1").Select
    
'Copier les données Flux 1
    Windows("Fichier Synthèse OF - Flux 1.xlsm").Activate
    Sheets("Synthèse OF - Flux 1").Activate
    
    ActiveSheet.Range("$A$8:$AJ$10000").AutoFilter Field:=16, Criteria1:="<>#N/A"
    
    Range("D:D,J:J,P:P").Select
    Range("P1").Activate
    Selection.Copy
    
    Workbooks("(NE_PAS_MODIFIER)_PLANNING_MNT_Flux 1&2.xlsm").Worksheets("Data_MNT_1").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Rows("1:7").Select
    Range("A7").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Columns("B:B").Cut Destination:=Columns("D:D")
    Columns("A:A").Cut Destination:=Columns("E:E")
    
    Columns("A:B").Select
    Range("A1").Activate
    Selection.Delete Shift:=xlToLeft
    
    
'Création de la variable contenant le nombre de lignes
    Dim nb_lig_data As Long, nb_lig_planning As Long
    
    nb_lig_data = Range("B" & Rows.Count).End(xlUp).Row

'Déduire le délai de 7 jours ouvrés entre le besoin pour l'ilot et la date de début de préparation par le magasin
    Windows("(NE_PAS_MODIFIER)_PLANNING_MNT_Flux 1&2.xlsm").Activate
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2").Formula = "=IF(A2="""","""",WORKDAY(A2,-7))"
    
    Range("B2").AutoFill Destination:=Range("B2:B" & nb_lig_data)
    Range("B2:B" & nb_lig_data).Select
    
    
    
    Range("A1").Copy
    Range("B1").Select
    ActiveSheet.Paste
    
    Columns("B:B").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False


    
    Columns("A:A").Delete Shift:=xlToLeft

    Columns("A:A").NumberFormat = "dd/mm/yyyy"
    
    
'Mettre à jour la date du prochain Lundi sur le Planning pour MN_1
    Windows("(NE_PAS_MODIFIER)_PLANNING_MNT_Flux 1&2.xlsm").Activate
    Sheets("Date Lundi S+1").Range("K2").Copy
    Sheets("Planning pour MN_1").Activate
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data_MNT_1").Select
    Application.CutCopyMode = False
    
'Mettre sous forme de tableau
    Range("A7").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$" & nb_lig_data), , xlYes).Name = _
        "Tableau1"
    Range("Tableau1[#All]").Select
    ActiveSheet.ListObjects("Tableau1").TableStyle = "TableStyleLight9"
    Range("B7").Select
    ActiveSheet.ListObjects("Tableau1").Name = "bd"
    

    Range("A5").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "bd_1", Version:=6).CreatePivotTable TableDestination:="Data_MNT_1!R5C6", _
        TableName:="Tableau croisé dynamique1", DefaultVersion:=6
    Sheets("Data_MNT_1").Select
    Cells(5, 6).Select
   With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Reference")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("OF")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Date début planifiée")
        .Orientation = xlRowField
        .Position = 3
    End With
    
    
'Mettre le TCD sous format tabulaire
    ActiveSheet.PivotTables("Tableau croisé dynamique1").RowAxisLayout xlTabularRow
    
'Enlever les sous-totaux du TCD
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("OF"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Reference"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Date début planifiée").Subtotals = Array(False, False, False, False, False, False, _
        False, False, False, False, False, False)
        
'Enlever les lignes vides ou sans dates du TCD
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Date début planifiée")
        .PivotItems("").Visible = False
    End With

    
'Mise en place des formules dans le Planning pour MN_1 à partir du TCD
    Sheets("Planning pour MN_1").Select
    
    nb_lig_planning = Range("B" & Rows.Count).End(xlUp).Row
    
    Range("C3").Formula = _
        "=IFERROR(VLOOKUP($B3,bd_1[[#All],[Reference]],1,0),""X"")"
    Range("C3").Select
    Selection.AutoFill Destination:=Range("C3:C" & nb_lig_planning)
    Range("C3:C" & nb_lig_planning).Select
    
    
    Range("D3").FormulaArray = _
        "=IFERROR(INDEX(bd_1[[OF]:[OF]],SMALL(IF((bd_1[[Date début planifiée]:[Date début planifiée]]=R2C)*(bd_1[[Reference]:[Reference]]=RC2),ROW(R3C3:R164C3)-2),COUNTIF(R3C2:RC2,RC2))),"""")"
    Range("D3").AutoFill Destination:=Range("D3:D" & nb_lig_planning)
    Range("D3:D" & nb_lig_planning).Select
    Selection.AutoFill Destination:=Range("D3:M" & nb_lig_planning), Type:=xlFillDefault
    Range("D3:M" & nb_lig_planning).Select
    Range("B3").Select
    
    Sheets("Data_MNT_1").Visible = False

    
    'Supprimer les données dans le planning transmis au magasin
    Sheets("Planning MNT Flux 1").Select
    Cells.Delete Shift:=xlUp


    'Filter pour avoir que les lignes avec les préparations nécessaires
        Sheets("Planning pour MN_1").Activate
        ActiveSheet.Range("$A$2:$N$" & nb_lig_planning).AutoFilter Field:=14, Criteria1:="<>"

    'Copier les lignes nécessaires
            Sheets("Planning pour MN_1").Select
            Range("A2").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            
            'Coller dans l'autre feuille ACM pour avoir le strict minimum des données
            Sheets("Planning MNT Flux 1").Select
            Range("A2").Select
            ActiveSheet.Paste
            Columns("A:N").ColumnWidth = 18.5
            Columns("C:C").ColumnWidth = 0.1
            Range("A2").Select
    
    'Masquer colonne C pour éviter les doublons visuels et enlever les filtres
    Sheets("Planning pour MN_1").Select
    Columns("C:C").EntireColumn.Hidden = False
    
'Enregister et fermer planning pour MNT
Sheets("Date Lundi S+1").Range("K7") = Now
    'ThisWorkbook.Save
    'Application.Quit

'Mettre l'heure de la mise à jour
Windows("Fichier Synthèse OF - Flux 1.xlsm").Activate
Range("D8").Select

'Sheets("Tableau de bord Flux 1").Range("C30") = Now

'Sauvegarder
    'ThisWorkbook.Save
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.DisplayStatusBar = True
    
End Sub

Je suis dans l'incompréhension la plus totale, cel fait 1 semaine que j'essaye d'identifier la source du problème.
Je vous remercie d'avance pour vos tentatives et le temps que vous y consacrez.

Bien cordiakement.

ERIC S · 2 Août 2022

Re
la formule modifiée (à vérifier)

ERIC S · 2 Août 2022

Bonjour

difficile d'entrer dans ta tête (un fichier complet ne facilite pas les choses quand on ne connait pas le process...)
que veux-tu dire par

PROBLEME: à l'étape 3, l'ensemble des données sont bien extraites du fichier des OF et bien renseignées dans le TCD, cependant, elle ne se mettent pas tous en place dans le tableau avec les formules matricielles. (exemple: la référence 245310, ce n'est pas la seule.)

245310 apparait dans les lignes 3 à 12. Peut-être faut-il regarder du côté de la gestion du filtrage de la feuille Planning pour MN_1

Cotriana · 2 Août 2022

ERIC S à dit:
Bonjour

difficile d'entrer dans ta tête (un fichier complet ne facilite pas les choses quand on ne connait pas le process...)
que veux-tu dire par

245310 apparait dans les lignes 3 à 12. Peut-être faut-il regarder du côté de la gestion du filtrage de la feuille Planning pour MN_1

J'entend par là que lorsque je saisie des dates pour la 245310 dans l'outil 1, je lance ma macro, les OF ne vont pas apparaitre dans le planning, alors que d'autres oui.

J'ai poser le travail que j'ai effectué à présent mais si vous voyez une solution plus simple je suis preneur 🙂

ERIC S · 2 Août 2022

re

si tu défiltres la feuille Planning pour MN_1, tes OF sont bien présents ou pas (moi j'ai l'impression que oui) ?

Cotriana · 2 Août 2022

ERIC S à dit:
re

si tu défiltres la feuille Planning pour MN_1, tes OF sont bien présents ou pas (moi j'ai l'impression que oui) ?

certains oui, mais pas tous.
Vous pouvez faire une tentative en renseignant quelques dates (17/08; 18/08; 19/08) au début de l'outil, au milieu et à la fin, vous allez voir que tout les OF ne remontent pas.

ERIC S · 2 Août 2022

Re

c'est ta formule matricielle qui génère l'erreur, je regarde

Cotriana · 2 Août 2022

ERIC S à dit:
Re

c'est ta formule matricielle qui génère l'erreur, je regarde

Je viens de remarquer que celle-ci contient LIGNE($C$3:$C$164)-2), alors que j'ai 3502 lignes, j'ai tenté de modifier le 164 par 3502, mais en modifiant il n'y a rien qui apparait dans le tableau

Le 164 fait suite à un tableau initialement créée qui n'avait que 164 lignes dont j'ai adapté pour correspondre à mon flux

ERIC S · 2 Août 2022

Re

LIGNE(bd_1[Reference])-1 devrait marcher

Cotriana · 2 Août 2022

ERIC S à dit:
Re

LIGNE(bd_1[Reference])-1 devrait marcher

Après modification du code, je me retrouve avec un tableau vide, cela marche pour vous ?

ERIC S · 2 Août 2022

Re
la formule modifiée (à vérifier)

Cotriana · 2 Août 2022

ERIC S à dit:
Re
la formule modifiée (à vérifier)

Parfait merci, exactement ce qu'il me fallait. Bonne après-midi à vous !

ERIC S · 2 Août 2022

Re et fin (je pense)

Bonus
comme je suis fâché avec les formules matricielles (je n'ai pas trop l'occasion de les utiliser et donc c'est loin d'être un automatisme, et je trouve donc, par manque d'habitude, le debug "compliqué", voici la façon dont j'aurais abordé le pb :

-une colonne supplémentaire sur le tableau de donnée
-pas de formule matricielle sur le planning

XL 2016 Remplir automatiquement un tableau d'après des conditions et des valeurs

Cotriana

XLDnaute Nouveau

Pièces jointes

ERIC S

ERIC S

XLDnaute Barbatruc

Cotriana

XLDnaute Nouveau

ERIC S

XLDnaute Barbatruc

Cotriana

XLDnaute Nouveau

ERIC S

XLDnaute Barbatruc

Cotriana

XLDnaute Nouveau

ERIC S

XLDnaute Barbatruc

Cotriana

XLDnaute Nouveau

ERIC S

XLDnaute Barbatruc

Pièces jointes

Cotriana

XLDnaute Nouveau

ERIC S

XLDnaute Barbatruc

Pièces jointes

Discussions similaires

XL 2016 Remplir automatiquement un tableau d'après des conditions et des valeurs

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

Discussions similaires

Privacy & Transparency

Privacy & Transparency