Progammation VBA ou macro, projet complexe

[fx83]

Programmation VBA ou macro, Mise en forme, copier-coller, analyse

Bonjour à toutes et à tous en cette nouvelle année 2008...

Mes meilleurs voeux pour vous toutes et tous !!

Je commence cette nouvelle année par un projet complexe et j'aurai bien besoin d'un coup de main.

Je vous mets un fichier joint afin de vous faire une idée plus précise de ma demande.

Il s'agit d'un fichier permettant l'analyse des heures effectuées par affaires par des employés d'un secteur en fonction des heures vendues dans les contrats.
Il existe 2 types de contrat qui m'intéressent, le P2 et le P3 (voire fichier joint pour comprendre). Les secteurs sont 210, 220, 230, 240 etc.
Les feuilles correspondantes aux heures par secteur et par contrat ("H210", "H220", etc)sont extraites d'un autre logiciel et sont importées manuellement.

Donc, voici ma demande:

1°) La feuille "suivi" est un simple copier-coller d'un fichier excel déja existant sur mon disque dur. J'aimerai automatiser ce copier-coller sachant que le nom du fichier change en fonction de sa mise à jour. Voici un exemple de son nom: "Tableau des affaires v191107"

Peut-être simplement mettre en place un bouton avec une macro permettant d'aller chercher le fichier manuellement par l'explorateur, de l'ouvrir, de selectionner l'intégralité du contenu, de le copier, de le coller dans ma feuille "suivi", de le refermer sans l'enregistrer puis de selectionner la feuille "Analyse" de mon fichier actuel.

2°) La feuille "Analyse":
* La structure:
- en A1, la date du jour.
-des tableaux reprenant les affaires par secteur (210, 220, 230 etc) de la feuille "suivi". Les colonnes à copier sont D, E, J, K, L, M & N. La partie jaune des tableaux de la feuille "Analyse" a été rajoutée manuellement et j'aimerai que cela soit automatisé svp.
- des boutons de formulaires "Analyse secteur xxx" afin d'automatiser la récupération des données par secteur, de mettre à jour les tableaux et de faire les analyses automatiquement avec un simple clique.

* Ma demande:
- Créer une macro ou programmer en VBA ou autre solution permettant d'automatiser au max le traitement de la feuille "Analyse".
- Tous les mois un opérateur devra pouvoir importer le tableau des affaires dans la feuille "suivi", il devra pouvoir en cliquant sur les boutons faire apparaitre les tableaux des affaires par secteur sachant que le nombre d'affaires est amené à varier. Récupérer les valeurs des feuilles d'heures qui nous intéressent (Heures P2 et P3 du dernier mois & heures totales P2 et P3) et les renseigner dans le tableau de la feuille "Analyse".
- La feuille "Analyse" sera imprimée.
- Le secteur 260 ne sera pas analysé.

Merci de votre aide et si vous avez des questions je suis là... à mon avis ca va fuser !!!

 

[fx83]

Re : Progammation VBA ou macro, projet complexe

Bonsoir Bebere, Skoobi, le forum,

Merci pour ta proposition Skoodi je vais regarder ça.

Les heures dont je parle sont contenues à la base dans un logiciel de saisi externe. Ce logiciel permet la saisie et le suivi des heures sur une année comptable complète de mars à mars.
De ce logiciel, on extrait des fichiers excel par secteur tous les mois.
On aura donc 6 classeurs excel avec les heures effectuées sur chaque secteur (210,220,230,240,250,270)et ce par affaire et par type de contrat (p2,p3 et p5).
Ces 6 classeurs sont ensuite intégrés manuellement au fichier actuel, dans chacune des feuilles prévues (H210, H220 etc.). Les heures contenues dans ces feuilles sont donc mensuelles et en ligne 8 apparaisse les mois de l'année.

Les heures sont ensuite récupérées et intégrées (automatiquement par VBA d'ou ma demande ici) au tableau de la feuille "Analyse" du classeur que vous traitez en ce moment. Nous faisons un suivi tous les débuts de mois. Nous analysons donc les heures du mois précédent le mois en cours.

Les heures de la feuille "suivi" sont les heures vendues sur la durée du contrat et servent uniquement de référence pour comparer la consommation des heures.

Est-ce plus clair ?!

Edit: je viens de tester ton programme Skoobi, c'est presque parfait.
Merci beaucoup, je m'attendais à ce que ce soit bien plus compliqué que ça.

Serait-il possible svp:
1°) que le programme fasse l'analyse du nombre d'heures précédent le mois en cours en se basant sur la date figurant en A1 de la feuille "Analyse".

2°) que la mise en forme du tableau "Analyse" s'adapte à la longueur des lignes.

3°) que le programme calcule l'écart entre heures totales en colonne H et heures totales en colonne K et mette ce résultat dans la colonne L, écart heure.

4°) que les écarts négatifs soient facilement repérable (par ex. coloriser le fond de la cellule)

5°) que les lignes soient classées par secteur du 210 au 270.

Merci beaucoup.

 

[Bebere]

Re : Progammation VBA ou macro, projet complexe

bonjour Fx,Scoobi
suite du précédent
à bientôt

 

[fx83]

Re : Progammation VBA ou macro, projet complexe

Bonjour Bebere, Skoobi, le forum,

Merci Bebere pour ta proposition.

Je viens de la tester mais cela ne marche visiblement pas lorsque j'ai plusieurs affaires par secteur. La macro continue à analyser uniquement 2 affaires (UTS mayol et Blachere).
De plus, le programme n'écrase pas le tableau de la feuille "Analyse" mais copie les lignes les unes en dessous des autres.

Par contre, ton programme fait bien le calcul d'écart des heures et prend en compte la modification du mois à analyser.

A ce sujet comment faire pour que la modification du mois se fasse automatiquement sans rentrer dans le visual basic ?

Je suis sur qu'à vous deux Bebere et Skoobi, vous avez la solution de toute ma demande.

Merci à vous de m'aider.

 

[skoobi]

Re : Progammation VBA ou macro, projet complexe

Re tout le monde,

les points 2 à 5 sont faits.
Pour le point 1, il faudrait que tu fasse un exmple pour que l'on sache où récupérer les heures des autres mois dans les feuilles "secteurs". Est-ce que ces feuilles sont "écrasées" avec les valeurs du nouveau mois où bien tu les places en-dessous?

 

[fx83]

Re : Progammation VBA ou macro, projet complexe

Re tout le monde,

les points 2 à 5 sont faits.
Pour le point 1, il faudrait que tu fasse un exmple pour que l'on sache où récupérer les heures des autres mois dans les feuilles "secteurs". Est-ce que ces feuilles sont "écrasées" avec les valeurs du nouveau mois où bien tu les places en-dessous?

Re Skoobi,

Je vais tester ça, en tout cas un grand merci pour cette avancée et ta proposition.

Pour le point 1, je t'ai mis un fichier joint.
Sur la feuille H210, regarde les cellules dont le fond est bleu. Il s'agit des mois de l'année (03, 04 , 05 .... , 02). Les mois se trouvent sur la ligne 8. Dans la colonne ensuite de chaque mois, se trouvent les heures effectuées par contrat et par type de prestation (P2, P3 etc).

J'aimerai donc que lorsqu'on ouvre le fichier et que la date en A1 de la feuille "Analyse" indique janvier, que les heures transmises dans le tableau analyse soient celles de décembre (colonne Y-Z). Qu'en février, on récupère les heures de janvier (colonne AA-AB)des feuilles d'heures "Hxxx" pour les mettre dans le tableau analyse. Qu'en mars, on récupère les heures de février (colonne AC-AD) etc.

Les feuilles d'heures par secteur "Hxxx" sont écrasées tous les mois.

J'espere avoir répondu à tes questions.

 

[skoobi]

Re : Progammation VBA ou macro, projet complexe

Re fx83,

voici la version 3 avec la prise en compte du mois .
Bon test.

 

[fx83]

Re : Progammation VBA ou macro, projet complexe

Re fx83,

voici la version 3 avec la prise en compte du mois .
Bon test.

Bonsoir Skoobi, le forum, bebere,

Merci pour cette modification Skoobi.

Je viens de faire le test avec le fichier des affaires complet et ca me donne une erreur d'éxécution:

Erreur d'éxecution '13'
Incompatibilité de type

Lorsque je fais débogage, ca met une erreur sur la ligne suivante:

.Range("H" & contrat.Row) = .Range("F" & contrat.Row) + .Range("G" & contrat.Row)

Je remarque aussi d'autre détails:

- lorsque je reclique sur le bouton "analyse", les données du tableau analyse ne sont pas écrasées. Je dois d'abord effacer le contenu du tableau manuellement pour le mettre à jour.

- la mise en forme du tableau n'est pas reproduite (partie verte et partie jaune)

- les colonnes I et J du tableau analyse ne sont pas renseignées correctement, il n'y a que des 0.

Je vous mets en lien, le fichier complet afin de voir par vous même le bug.

Lien ici

Merci

 

[skoobi]

Re : Progammation VBA ou macro, projet complexe

Re bonjour,


voici le code corrigé.

Option Explicit
Sub Analyse()
Dim Ws As Worksheet, Num As Variant, mois As Long, moisC As Long, H, I, J
Dim Cel As Range, CelR As Range, Rng As Range, contrat As Range
[B][COLOR="Green"]Application.ScreenUpdating = False[/COLOR][/B]
mois = Month(Sheets("Analyse").Range("A1").Value)
If mois = 1 Then
    mois = 12
Else: mois = mois - 1
End If
With Worksheets("Suivi")
    Set Rng = .Range("D5:D" & .Range("D65536").End(xlUp).Row) 'liste n° contrat
End With
For Each CelR In Rng
    If CelR <> "" Then
        Num = CelR.Value 'n° contrat
        For Each Ws In Worksheets
            If Left(Ws.Name, 1) = "H" Then
                With Ws
                    moisC = .Cells.Find(mois, LookIn:=xlValues, lookat:=xlWhole).Column
                    Set Cel = .Cells.Find(Num, LookIn:=xlValues, lookat:=xlPart)
                    If Not Cel Is Nothing Then
                        If .Cells(Cel.Row, 5) = "P2" Then H = H + .Cells(Cel.Row, moisC)
                        If .Cells(Cel.Row + 1, 5) = "P3" Then I = I + .Cells(Cel.Row + 1, moisC)
                        If .Cells(Cel.Row, 5) = "P2" Then J = J + .Cells(Cel.Row, "AE")
                        If .Cells(Cel.Row + 1, 5) = "P3" Then J = J + .Cells(Cel.Row + 1, "AE")
                        Exit For
                    End If
                End With
            End If
        Next Ws
        With Worksheets("Analyse")
            Set contrat = .Cells.Find(Num)
            If contrat Is Nothing Then
                Set contrat = .Range("B65536").End(xlUp).Offset(1, 0)
                contrat.Offset(0, -1).Value = CelR.Offset(0, -3).Value
                contrat.Value = Num
                contrat.Offset(0, 1).Value = CelR.Offset(0, 1).Value
            End If
 [B][COLOR="Green"]           .Range("D" & contrat.Row) = Val(CelR.Offset(0, 6).Value)
            .Range("E" & contrat.Row) = Val(CelR.Offset(0, 7).Value)
            .Range("F" & contrat.Row) = Val(CelR.Offset(0, 8).Value)
            .Range("G" & contrat.Row) = Val(CelR.Offset(0, 9).Value)[/COLOR][/B]
            .Range("H" & contrat.Row) = .Range("F" & contrat.Row) + .Range("G" & contrat.Row)
            .Range("I" & contrat.Row) = H
            .Range("J" & contrat.Row) = I
            .Range("K" & contrat.Row) = J
            .Range("L" & contrat.Row) = .Range("H" & contrat.Row) - .Range("K" & contrat.Row)
            If .Range("L" & contrat.Row).Value < 0 Then
                .Range("L" & contrat.Row).Interior.ColorIndex = 8 'cyan
            Else: .Range("L" & contrat.Row).Interior.ColorIndex = 6 'jaune
            End If
            '.Range("K8") = K
        End With
    End If
    H = 0
    I = 0
    J = 0
Next CelR
Columns("A:L").AutoFit
With Worksheets("Analyse")
    .Range(.[A8], .Range("L8").End(xlDown)).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlNo
End With
[B][COLOR="Green"]Application.ScreenUpdating = True[/COLOR][/B]

End Sub

lorsque je reclique sur le bouton "analyse", les données du tableau analyse ne sont pas écrasées. Je dois d'abord effacer le contenu du tableau manuellement pour le mettre à jour.

Comme le programme avait un problème c'est normal.

la mise en forme du tableau n'est pas reproduite (partie verte et partie jaune)

Je penses que tu peux y arriver.

les colonnes I et J du tableau analyse ne sont pas renseignées correctement, il n'y a que des 0

C'est car pour le mois n-1 il n'y pas d'heures sur les contrats concernés non?
C'est bien ce que tu voulais non?

 

[fx83]

Re : Progammation VBA ou macro, projet complexe

Bonjour Skoobi,

Merci pour ces corrections.

En ce qui concerne la mise en forme du tableau (bordures, partie verte et jaune), je ne sais pas comment programmer en VBA le fait qu'elle s'adapte à la longueur du tableau. Vu que celui-ci sera imprimer, je ne souhaite imprimer 10 pages si seulement 5 sont remplies.

Et en ce qui concerne les "0", mea culpa, tu as totalement raison.

J'aurai une demande supplémentaire svp:

- Certaines affaires de la feuille "suivi" sont résiliées. Elles apparaissent tout en bas de la feuille de couleur rouge. Les affaires résilisées n'apparaissent pas dans les feuilles d'heures "Hxxx".
Serait-il possible de mettre en place un test du style: si l'affaire n'est pas dans les feuilles d'heures, on la traite pas (pas affichée dans le tableau analyse).


Merci de votre aide.

 

[skoobi]

Re : Progammation VBA ou macro, projet complexe

Re fx83,

Serait-il possible de mettre en place un test du style: si l'affaire n'est pas dans les feuilles d'heures, on la traite pas (pas affichée dans le tableau analyse)

Si cette affaire existe dans le tableau analyse (avant qu'elle soi résilié), il faut la supprimer?

C'est fait pour la mise en forme.

 

[fx83]

Re : Progammation VBA ou macro, projet complexe

Re fx83,

Si cette affaire existe dans le tableau analyse (avant qu'elle soi résilié), il faut la supprimer?

C'est fait pour la mise en forme.

Si l'affaire est présente dans le tableau analyse puis qu'on la résilie, il faudra la supprimer également du tableau analyse vu qu'on aura plus besoin d'analyser les heures sur cette affaire.

Edit: je viens de tester le fichier et ca me refait le meme bug qu'avec l'ancien:

erreur de compilation '13'

 

[skoobi]

Re : Progammation VBA ou macro, projet complexe

Re,

je viens de tester le fichier et ca me refait le meme bug qu'avec l'ancien

Oupsss, désolé je ne l'avais pas intégré dans mon fichier.

Voici la v5 avec ta dernière demande.

Bon test

 

[fx83]

Re : Progammation VBA ou macro, projet complexe

Bonjour Skoobi, le forum,

Merci Skoobi pour cette dernière version, on y est presque. Il manque juste 3 détails.

1°) les cellules d'écarts d'heures négatifs en colonne L de la feuille analyse ne sont pas colorisées.

2°) Est-il possible, qu'entre chaque sous secteur de la feuille analyse, la bordure inférieure de la ligne entre les colonnes A et L, soient grasse afin de voir la différence visuellement.

3°) La largeur des colonnes à la fin de la mise en page est trop importante pour tout faire rentrer sur une seule page pour l'impression. Est-il possible de spécifier la largeur de chaque colonne. j'ai fait des tests et il faudrait les largeurs suivantes:
- A ->10
- B -> 10
- C -> 40
- D à L -> 8

Merci pour ton aide Skoobi.

 

[skoobi]

Re : Progammation VBA ou macro, projet complexe

Re,

avec toutes les demandes en plus par rapport au 1er post ça fera 100 euros .

Voici la v6.

 

[fx83]

Re : Progammation VBA ou macro, projet complexe

Merci Skoobi pour ton travail et ton aide. C'est toujours agréable de voir des personnes disponibles et prêtes à aider leur prochain.