IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Feuille Bilan avec VBA


Sujet :

Macros et VBA Excel

  1. #1
    Nouveau Candidat au Club
    Femme Profil pro
    assitant chargé d'affaire
    Inscrit en
    Décembre 2016
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 32
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : assitant chargé d'affaire

    Informations forums :
    Inscription : Décembre 2016
    Messages : 4
    Points : 1
    Points
    1
    Par défaut Feuille Bilan avec VBA
    Bonjour,

    Je viens vous dérangez sur ce forum car j'ai vraiment besoin d'aide.

    Je suis totalement novice avec VBA (c'est le deuxième fichier que je fais et je galère beaucoup ) alors désolée si je ne suis pas hyper précise en plus ce que je demande me parait vraiment compliqué.

    Voilà mon problème j'ai fais un fichier qui utilise un userform qui remplit plusieurs onglets (jusque la tout va bien).

    Ce fichier va nous permettre de rentrer les heures du personnel.

    Je voudrais faire un feuille bilan (un classeur excel= 1 mois) en faisant le total des heures par personne par jour et par code chantier ( oui je suis chiante mais je suis une femme j'ai le droit )

    d'habitude j'ai un supercollègue qui m'aide avec VBA sauf qu'aujourd'hui il n'est pas la, du coup je dois avouer que je suis totalement pommée et que je n'ai strictement aucune mais alors AUCUNE idée de comment faire ça.

    J'espère que j'ai été claire et m'excuse d'avance si je ne comprends pas tout.

  2. #2
    Expert éminent
    Avatar de MarcelG
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Juillet 2009
    Messages
    3 449
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 66
    Localisation : France, Maine et Loire (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Finance

    Informations forums :
    Inscription : Juillet 2009
    Messages : 3 449
    Points : 7 149
    Points
    7 149
    Billets dans le blog
    7
    Par défaut
    Bonjour et bienvenue sur le Forum,

    Pourrais-tu retourner un exemplaire simplifié de ton classeur, car l'on n'en voit pas la structure.

    Bien Cordialement.

    Marcel

    Dernier billet:
    Suppression des doublons d'un tableau structuré, gestion d'un array

    Pas de messagerie personnelle pour vos questions, s'il vous plaît. La réponse peut servir aux autres membres. Merci.


  3. #3
    Nouveau Candidat au Club
    Femme Profil pro
    assitant chargé d'affaire
    Inscrit en
    Décembre 2016
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 32
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : assitant chargé d'affaire

    Informations forums :
    Inscription : Décembre 2016
    Messages : 4
    Points : 1
    Points
    1
    Par défaut
    Bonjour et merci

    Alors c'est compliqué de vous envoyé mon fichier car c'est un fichier préparatoire pour l'instant aucune données n'est renseigné et la feuille Bilan que je souhaite faire j'aimerai que le tableau se fasse automatiquement lorsqu'on remplit les autres onglets (désolée j'ai oublié de le préciser) du coup l'onglet est totalement vide

  4. #4
    Expert éminent
    Avatar de MarcelG
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Juillet 2009
    Messages
    3 449
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 66
    Localisation : France, Maine et Loire (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Finance

    Informations forums :
    Inscription : Juillet 2009
    Messages : 3 449
    Points : 7 149
    Points
    7 149
    Billets dans le blog
    7
    Par défaut
    Salut,

    Juste un exemple de classeur avec des données imaginaires.

    Bien Cordialement.

    Marcel

    Dernier billet:
    Suppression des doublons d'un tableau structuré, gestion d'un array

    Pas de messagerie personnelle pour vos questions, s'il vous plaît. La réponse peut servir aux autres membres. Merci.


  5. #5
    Nouveau Candidat au Club
    Femme Profil pro
    assitant chargé d'affaire
    Inscrit en
    Décembre 2016
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 32
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : assitant chargé d'affaire

    Informations forums :
    Inscription : Décembre 2016
    Messages : 4
    Points : 1
    Points
    1
    Par défaut
    Voila le fichier j'ai enlevé le userform l'onglet 24099 correspond à un code chantier il est remplit avec des données imaginaires et l'onglet bilan avec le résultat que je voudrais obtenir.

    Est ce que c'est ok comme ça ?
    Fichiers attachés Fichiers attachés

  6. #6
    Expert éminent
    Avatar de MarcelG
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Juillet 2009
    Messages
    3 449
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 66
    Localisation : France, Maine et Loire (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Finance

    Informations forums :
    Inscription : Juillet 2009
    Messages : 3 449
    Points : 7 149
    Points
    7 149
    Billets dans le blog
    7
    Par défaut BILAN
    Bonjour Elow_dy et Bonjour au Forum,

    Mes excuses pour cette réponse tardive.

    En prenant pour base, ta pièce jointe.

    La méthode :

    - Tableau des feuilles de données composant le classeur
    - Pour chacune d'elle, définitions des noms correspondants aux dates, collaborateurs et heures
    - Dictionnaire et tri des dates rencontrées
    - Pour chaque item du dictionnaire des dates, recherche du(des) collaborateurs(s) associé(s)
    - Dans la feuille BILAN,
    En lignes : couples date/collaborateur
    En colonnes : noms des chantiers (= nom des feuilles)
    Calcul du nombre d'heures par utilisation de la fonction Sommeprod en y intégrant les 3 variables ci-dessus (noms de champs)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    Option Explicit
    Option Base 1
     
    Sub essai()
     
    Dim dico_dates As Variant
    Set dico_dates = CreateObject("Scripting.Dictionary")
     
    Dim tablo_wks()
     
    Dim wks As Worksheet
    Dim n As Byte
    Dim derdate As Integer, i As Integer
     
    n = 0
     
    Worksheets("BILAN").Range("C2").CurrentRegion.ClearContents
     
    'Création du tableau des feuilles du classseur
    For Each wks In ThisWorkbook.Worksheets
     
            With wks
     
                    If InStr(.Name, "BILAN") = 0 Then
     
                            n = n + 1
     
                            ReDim Preserve tablo_wks(1 To n)
                            tablo_wks(n) = .Name
     
                            'nom de la feuille en en-tête de champ dans la feuille billan
                            Worksheets("BILAN").Cells(1, 2 + n).Value = .Name
                            derdate = .Cells(.Rows.Count, 1).End(xlUp).Row
                            derdate = WorksheetFunction.Max(derdate, 2)
     
                            'Création des noms utilisés dans la fonction Sommeprod
                            ThisWorkbook.Names.Add Name:="liste_dates_" & .Name, RefersTo:=.Range("A2", .Cells(derdate, 1))
                            ThisWorkbook.Names.Add Name:="liste_collab_" & .Name, RefersTo:=.Range("liste_dates_" & .Name).Offset(0, 1)
                            ThisWorkbook.Names.Add Name:="liste_heures_" & .Name, RefersTo:=.Range("liste_dates_" & .Name).Offset(0, 5)
     
                            'Dictionnaire des dates pour l'ensemble des feuilles
                            For i = 2 To derdate
                                    If Not dico_dates.Exists(.Cells(i, 1).Value) Then dico_dates.Add .Cells(i, 1).Value, .Cells(i, 1).Value
                            Next i
     
                    End If
     
            End With
     
    Next wks
     
    Dim temp As Variant
    temp = dico_dates.items
     
    'Réinitialisation et Libération de la variable
    dico_dates.RemoveAll
    Set dico_dates = Nothing
     
    'Tri des dates par ordre coissant
    Call Tri(temp, LBound(temp), UBound(temp))
     
    Dim t As Variant
    Dim c As Range
    Dim firstAddress As String
    Dim p As Integer, n_occ As Byte
    Dim tablo() As Variant
    Dim cell_dest As Range
     
    'nombre de lignes informées dans la feuille "BILAN"
    n_occ = 0
     
    'Balayage des dates
    For Each t In temp
            'Debug.Print "Valeur de t : " & t
            'Recjerche de la date dans chaque feuille et eport du nom de collaborateur associé
            For Each wks In ThisWorkbook.Worksheets
                    With wks
                            If InStr(.Name, "BILAN") = 0 Then
                                    p = 0
                                    With .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
                                            'Trouve la date et l'inscrit dans la feuille bilan
                                            Set c = .Find(t, LookIn:=xlValues)
                                            If Not c Is Nothing Then
                                                    firstAddress = c.Address
                                                    Do
                                                            'incrémentation
                                                            p = p + 1
                                                            With c
                                                                    'test 1er enregistrement
                                                                    If p > 1 Then
                                                                            'test si doublon
                                                                            If CDate(.Value) <> tablo(1, p - 1) Or .Offset(0, 1).Value <> tablo(2, p - 1) Then
                                                                                    'report de la date et du nom associé
                                                                                    ReDim Preserve tablo(1 To 2, 1 To p)
                                                                                    tablo(1, p) = CDate(.Value)
                                                                                    tablo(2, p) = .Offset(0, 1).Value
                                                                                    Debug.Print tablo(2, p)
                                                                             Else
                                                                                    p = p - 1
                                                                            End If
                                                                    Else
                                                                            ReDim tablo(1 To 2, 1)
                                                                            tablo(1, p) = CDate(.Value)
                                                                            tablo(2, p) = .Offset(0, 1).Value
                                                                    End If
                                                                    'Debug.Print p
                                                                    'Debug.Print tablo(1, p)
                                                                    'Debug.Print tablo(2, p)
                                                            End With
                                                            Set c = .FindNext(c)
                                                    Loop While Not c Is Nothing And c.Address <> firstAddress
                                            End If
                                            Set c = Nothing
                                    End With
                            End If
                    End With
            Next wks
            'Destination du tableau date/collaborateur(s) dans la feuille BILAN
            With Worksheets("Bilan")
                    Set cell_dest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
            End With
            cell_dest.Resize(UBound(tablo, 2), UBound(tablo, 1)).Value = WorksheetFunction.Transpose(tablo)
            'nombre d'occurences incrémentées
            n_occ = n_occ + UBound(tablo, 2)
            Erase tablo
    Next t
     
    'Debug.Print "n occ : " & n_occ
     
    Dim j As Byte
    Dim laréf As String
    Dim d As Range
    Dim cells_formules As Range
     
    With Worksheets("BILAN")
            For j = LBound(tablo_wks, 1) To UBound(tablo_wks, 1)
                    laréf = tablo_wks(j)
                    'Debug.Print laréf
                    'Debug.Print .Range(.Cells(2, j + 2), .Cells(n_occ + 1, j + 2)).Address
                    'Adresse des formules à créer
                    Set cells_formules = .Range(.Cells(2, j + 2), .Cells(n_occ + 1, j + 2))
                    For Each d In cells_formules
                            With d
                                    'Formule Sommeprod avec comme variables le nom de la feuille et ceux des champs associés
                                    .FormulaR1C1 = "=SUMPRODUCT((liste_dates_" & laréf & "=RC[-" & j + 1 & "]+0) *(liste_collab_" & laréf & "=RC[-" & j & "])*(liste_heures_" & laréf & "))"
                                    'Collage spécial valeurs (astuce du grand mercatog que je salue au passage)
                                    .Value = .Value
                                    'Effacement si valeur = 0
                                    If .Value = 0 Then .ClearContents
                            End With
                    Next d
                    'libération de la variable
                    Set cells_formules = Nothing
            Next j
    End With
     
    End Sub
     
    '-----------------------------------------------------------------------
     
    'Procédure récursive de tri d'un dictionnaire
    Sub Tri(a As Variant, gauc As Long, droi As Long)   ' Quick sort
     
    Dim g As Long, d As Long
    Dim ref As Variant
    Dim t As Variant
     
    ref = a((gauc + droi) \ 2)
    g = gauc: d = droi
     
    Do
            Do While a(g) < ref
                    g = g + 1
            Loop
            Do While ref < a(d)
                    d = d - 1
            Loop
            If g <= d Then
                    t = a(g)
                    a(g) = a(d)
                    a(d) = t
                    g = g + 1
                    d = d - 1
            End If
    Loop While g <= d
     
    If g < droi Then Call Tri(a, g, droi)
     
    If gauc < d Then Call Tri(a, gauc, d)
     
    End Sub
    La méthode Find est ici une adaptation de l'aide en ligne pour celle-ci.
    AVANT de décortiquer ce code, il te vaudrait mieux, si nécessaire, de bien intégrer les notions de tableaux (voir espaces de Silkyroad essentiellement PUIS Didier Gonnard une fois la notion maîtisée), et de dictionnaires;

    Bien Cordialement.

    Marcel

    Dernier billet:
    Suppression des doublons d'un tableau structuré, gestion d'un array

    Pas de messagerie personnelle pour vos questions, s'il vous plaît. La réponse peut servir aux autres membres. Merci.


  7. #7
    Expert confirmé Avatar de BENNASR
    Homme Profil pro
    Responsable comptable & financier
    Inscrit en
    Décembre 2013
    Messages
    2 913
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Tunisie

    Informations professionnelles :
    Activité : Responsable comptable & financier
    Secteur : Finance

    Informations forums :
    Inscription : Décembre 2013
    Messages : 2 913
    Points : 5 119
    Points
    5 119
    Par défaut
    bonsoir à toutes et à tous
    pour moi je crois que excel nous donne un outil formidable qui est le tableau croisé dynamique : un fichier de base composé de la façon suivante
    matricule / nom et Prénom / journée / heure début / heure fin / chantier / mois / année
    pour le mois et l'année tu peux extraire via une formule excel
    un tableau dynamique croisée avec des segments comme ça tu trouveras tes données soit par chantier soit par agent soit par mois et tu aura ton bonheur
    chaque saisi tu actualise ton tableau et tout sera dans l'ordre
    Fichiers attachés Fichiers attachés
    --------------------------------------------------------------*****----------------------------------------------------------------------------
    Bonne Continuation & Plein Succès
    Notre seul pouvoir véritable consiste à aider autrui avec modestie
    ______________________________________________________
    Pour dire merci, cliquer sur et quand la discussion est résolue, penser à cliquer sur le bouton

  8. #8
    Expert éminent
    Avatar de MarcelG
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Juillet 2009
    Messages
    3 449
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 66
    Localisation : France, Maine et Loire (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Finance

    Informations forums :
    Inscription : Juillet 2009
    Messages : 3 449
    Points : 7 149
    Points
    7 149
    Billets dans le blog
    7
    Par défaut
    Salut,

    Pfff!

    A ceci près que les données ne sont pas reportées sur une seule base mais sont réparties sur plusieurs feuilles.
    C'est là l'unique nuance mais de taille!

    On peut certes "bidouiller" mais je ne trouve pas cela très "fun".

    Enfin, les notions de Tableau (Array) et Dictionnaire sont, une fois maîtrisées, bien précieuses.

    Bien Cordialement.

    Marcel

    Dernier billet:
    Suppression des doublons d'un tableau structuré, gestion d'un array

    Pas de messagerie personnelle pour vos questions, s'il vous plaît. La réponse peut servir aux autres membres. Merci.


  9. #9
    Nouveau Candidat au Club
    Femme Profil pro
    assitant chargé d'affaire
    Inscrit en
    Décembre 2016
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 32
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : assitant chargé d'affaire

    Informations forums :
    Inscription : Décembre 2016
    Messages : 4
    Points : 1
    Points
    1
    Par défaut merci
    Bonjour,

    Je viens seulement de me rendre compte que j'avais eu des réponses et je vous en remercie.

    Je vais essayer tout ça en essayant évidemment de comprendre ce que je fait

    Merci en tout cas

Discussions similaires

  1. [XL-2007] Aide sur l'envoi d'une feuille excel avec vba
    Par MelanieK dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 02/02/2012, 22h51
  2. Ajout d'un bouton dans une feuille excel avec vba
    Par nsqualli dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 25/11/2008, 16h35
  3. Réponses: 9
    Dernier message: 19/02/2007, 21h33
  4. [VBA-E]Sauvegarde feuille Excel avec VBA
    Par jojo2303 dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 12/03/2006, 20h06
  5. [VBA] Dissocier des feuilles Excel avec VBA
    Par lezinve dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 02/03/2006, 17h30

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo