Macro de vérification d'update de données

Version imprimable

Bonjour à tous !

J'ai un niveau très débutant en VBA, et le code obtenu est le résultat de parcours dans ce forum. Merci d'avance pour vos coups de main !

Je vais essayer de m'exprimer le plus clairement possible : l'objectif de ma macro est de parcourir un dossier de fichiers de domaines unitaires afin de vérifier s'ils ont rentré les données à une date précise, donc si l'update est fait (donnée non #N/A présente), on affiche "YES" avec le chiffre rentré et le mois en cours, et si non, afficher "NO" et remonter dans la colonne de la dite donnée pour chercher la dernière mise à jour effectuée, ainsi que le mois correspondant, donc voilà pour résumer.

La date du contrôle est rentrée par un userform et un combobox avec la liste des dates, puis après le choix et le clic sur OK, ma macro se lance. Elle commence par créer un onglet "template" avec comme non "Fin mm/aaaa" selon le choix effectué. Il y ensuite a l'étape choix du dossier ayant les fichiers à contrôler, ensuite le sub sur l'import des données commence et la partie ou je bloque c'est surtout les boucles à effectuer au sein du fichier de données, je n'arrive pas à trouver la bonne combinaison de boucles (If ? Do while ?) pour avoir le résultat que je désire.

Pour aider à comprendre le code, j'ai 3 types de données différentes, mais je cherche à récupérer leur somme (ex: la donnée 1 est sur 3 colonnes différentes, donc en résultat il faut la somme des données des 3 colonnes sur le mois correspondant). J'ai fait le code pour le 1er type de données uniquement, si je trouve une solution je pourrai l'étendre au reste.

J'espère avoir été assez clair, je joints le code du sub d'import ci-dessous en espérant qu'il soit assez compréhensible :

Code:

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
 
Sub import()
'Application.DisplayAlerts = False
 
MsgBox "Choisir le répertoire des domaines"
sNomRepertoire = ChoisirRepertoire ' demande de sélectionner le dossier contenant les fichiers domaines
If sNomRepertoire = "" Then
Exit Sub
End If
 
Path = sNomRepertoire
 
fichier = Dir(Path & "\*.xlsx ") 'définit les fichiers à importer en l'occurence tous les fichiers excel se trouvant dans ce répertoire
 
Do While fichier <> "" 'démarre la boucle jusqu'au dernier fichier disponible dans le répertoire
    Dim plage As Range 'plage de dates à balayer dans fichier KPI
    Dim c As Range
    Dim n As Range
    Dim i As Variant
    Dim donnee_123 As Variant
    Dim donnee_45 As Variant
    Dim donnee_67 As Variant
    Dim l_donnee_123 As Variant
    Dim l_donnee_45 As Variant
    Dim l_donnee_67 As Variant
    Dim l_date As Variant
    Dim vdate As Variant
    Set vplage = Sheets("Vérification").Range("A2:A78")
    Set WbSource = Workbooks.Open(Path & "\" & fichier) 'ouvre le fichier actuel à contrôler
    Set wksNewSheet = WbSource.Sheets("Données") 'sélectionne la feuille de données à contrôler
    Set plage = wksNewSheet.Range("A2:A66") 'l'ensemble des dates des fichiers domaines unitaires
 
 
    wksNewSheet.Activate 'active cette feuille
    domaine = wksNewSheet.Range("AA2").Value
    'MsgBox domaine
 
    For Each c In plage
        If c.Value = rdate Then
        ligne = c.Row
        'MsgBox ligne
            For Each n In vplage
                If n.Value = domaine Then
                    vligne = n.Row
                    'MsgBox vlign
 
WbSource.Activate
                    For Each i In Array(5, 6, 7) ', 13, 14, 23, 25)
                        If IsError(c(ligne, i).Value) Then
                            Do While IsError(c(ligne, i).Value)
                                ligne = ligne - 1
                            Loop
 
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 10) = "NO"
                            l_donnee_123 = c(ligne, i).Value + c(ligne, i + 1).Value + c(ligne, i + 2).Value
                            vdate = c(ligne, 1)
                            'MsgBox vdate
 
                            Else
                            donnee_123 = c(ligne, i).Value + c(ligne, i + 1).Value + c(ligne, i + 2).Value
                            'donnee_123 = WbSource.Worksheets("Données").Cells(ligne, i).Value + WbSource.Worksheets("Données").Cells(ligne, i + 1).Value + WbSource.Worksheets("Données").Cells(ligne, i + 2).Value
                            l_date = c(ligne, 1)
 
Verif.Activate
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 10) = "YES"
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 11) = l_date
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 12) = donnee_123
 
'Else ? 
 
'Verif.Activate
                            'Verif.Worksheets(Sheets.Count).Cells(vligne, 11) = vdate
                            'Verif.Worksheets(Sheets.Count).Cells(vligne, 12) = l_donnee_123
 
                        End If
                    Next i
                End If
            Next n
        End If
    Next c
 
    WbSource.Close 'ferme le fichier source
    fichier = Dir 'va vers le fichier suivant à importer
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 
'Une fois que la boucle fonctionne il faudra juste rajouter la recherche pour les 2 autres types de données
'        :=False, Transpose:=False
 
Loop 'recommence la boucle avec le fichier suivant
 
End Sub

02/09/2019, 08h33
eriiic

Bonjour,

Citation:

pour avoir le résultat que je désire.

C'est à dire ?
Quelle est l'anomalie constatée ?
eric
02/09/2019, 17h56
momzoo

Citation:

Envoyé par eriiic

Bonjour,

C'est à dire ?
Quelle est l'anomalie constatée ?
eric

Bonjour eriiic !

En fait la macro tourne mais les boucles ne fonctionnent pas. Donc mon tableau de données ne se remplit pas. Je ne sais pas si mes boucles sont "logiques" par rapport à ce que je veux faire.
J'ai déjà pu corriger une partie en remplaçant les c(ligne, i) par Cells(ligne, i) car la macro ne reconnaissait pas le "c".

Je peux partager le fichier et un fichier de données exemple pour que ce soit plus clair peut-être.
02/09/2019, 19h27
eriiic

Code:

If c.Value = rdate Then

rdate est une variable publique que tu as initialisée ailleurs ?
On ne le voit pas dans ton code.
Met un point d'arrêt ici pour contrôler sa valeur.
eric

Bonsoir,
La variable est dans les déclarations en effet mais pas dans le sub en question.

Voici le code en entier avec les corrections apportées jusqu'ici, il manque le userform mais cette partie ne pose pas trop problème :

Code:

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
 
Public DerLigne As Integer 'permet de trouver la dernière ligne
Dim ligne As Integer 'ligne correspondant à la date de vérif ds fichier domaine
Dim vligne As Integer 'ligne correspondant au domaine dans le fichier vérification
Public Date_Verif As Date 'la date choisie par l'utilisateur
Public Mois_Verif As Integer 'la mois correspondant à la date choisie
Public Annee_Verif As Variant 'l'année correspondant à la date choisie
Public rdate As String 'la date à rechercher ds le fichier de domaine
Public domaine As String 'le nom du domaine contrôlé
Public vplage As Range 'la plage des noms de domaine dans le fichier de vérification
Public Verif As Workbook  'fichier excel contenant la macro de vérification des données
Dim KPI As Workbook 'pour chaque fichier domaine qui s'ouvrira
Dim active As Workbook 'le fichier actif durant le run de la macro (test ou domaine)
Dim wksNewSheet As Worksheet 'le fichier domaine unitaire
Dim i As Variant 'variable utilisée pour boucle de recherche dans les lignes de données
Dim j As Variant 'variable utilisée pour boucle de recherche dans les colonnes de données
'
'
'
'
'
Sub Verif_Actuals()
Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
 
Set Verif = ActiveWorkbook 'on affecte le classeur à une variable pour faciliter l'utilisation
 
' On commence par effacer les données
    Verif.Activate
    Worksheets("Vérification").Range("J2:R76").ClearContents
    Worksheets("Tools").Range("E4:G4").ClearContents
    DerLigne = Sheets("Vérification").Range("A500").End(xlUp).Row 'trouve la dernière ligne de la colonne A avec des data
    Application.Calculation = xlCalculationAutomatic
    'On Error Resume Next
 
  Choix_date.Show
 
  Sheets.Add.Move After:=Sheets(Sheets.Count)
 
    Sheets(Sheets.Count).Name = "Fin " & Sheets("Tools").Range("F4") & "-" & Sheets("Tools").Range("G4")
    Worksheets("Vérification").Activate
    Sheets("Vérification").Range("A1:Z200").Copy
    Sheets(Sheets.Count).Paste
 
    rdate = Sheets("Tools").Range("E4") 'Pour stocker la date choisie sur une nouvelle variable
 
Call import
Verif.Activate
Sheets(Sheets.Count).Columns("A:AA").EntireColumn.AutoFit
MsgBox "La vérification est terminée"
Sheets(Sheets.Count).Activate
 
End Sub
'
'
'------------
'
'
Function ChoisirRepertoire() As String
Dim oFolder As Object
ChoisirRepertoire = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choisir un répertoire", 0)
If (Not oFolder Is Nothing) Then ChoisirRepertoire = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
'
'-------------
'
'
'
Sub import()
'Application.DisplayAlerts = False
 
MsgBox "Choisir le répertoire des KPI domain"
sNomRepertoire = ChoisirRepertoire ' demande de sélectionner le dossier contenant les fichiers domaines
If sNomRepertoire = "" Then
Exit Sub
End If
 
Path = sNomRepertoire
 
fichier = Dir(Path & "\*.xlsx ") 'définit les fichiers à importer en l'occurence tous les fichiers excel se trouvant dans ce répertoire
 
Do While fichier <> "" 'démarre la boucle jusqu'au dernier fichier disponible dans le répertoire
    Dim plage As Range 'plage de dates à balayer dans fichier KPI
    Dim c As Range
    Dim n As Range
    Dim i As Variant
    Dim donnee_123 As Variant
    Dim donnee_45 As Variant
    Dim donnee_67 As Variant
    Dim l_donnee_123 As Variant
    Dim l_donnee_45 As Variant
    Dim l_donnee_67 As Variant
    Dim l_date As Variant
    Dim vdate As Variant
    Set vplage = Sheets("Vérification").Range("A2:A78")
    Set WbSource = Workbooks.Open(Path & "\" & fichier) 'ouvre le fichier actuel à contrôler
    Set wksNewSheet = WbSource.Sheets("Données") 'sélectionne la feuille de données à contrôler
    Set plage = wksNewSheet.Range("A2:A66") 'l'ensemble des dates des fichiers domaines unitaires
 
 
    wksNewSheet.Activate 'active cette feuille
    domaine = wksNewSheet.Range("AA2").Value
    'MsgBox domaine
 
    For Each c In plage
        If c.Value = rdate Then
        ligne = c.Row
        'MsgBox ligne
            For Each n In vplage
                If n.Value = domaine Then
                    vligne = n.Row
                    'MsgBox vlign
 
WbSource.Activate
                    For Each i In Array(5, 6, 7) ', 13, 14, 23, 25)
                        If IsError(Cells(ligne, i).Value) Then
                            Do While IsError(Cells(ligne, i).Value) 'tant qu'on trouve un NA, remonter dans la colonne pour trouver une valeur non NA
                                ligne = ligne - 1
                            Loop
 
                            l_donnee_123 = Cells(ligne, i).Value + Cells(ligne, i + 1).Value + Cells(ligne, i + 2).Value 'variable faisant la somme des 3 valeurs qui va être remplie dans le fichier
                            vdate = Cells(ligne, 1) 'on récupère la date correspondant à la valeur
                            'MsgBox vdate
 
Verif.Activate
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 10) = "NO" 'Si on trouve un NA remplir NO
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 11) = vdate
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 12) = l_donnee_123
 
WbSource.Activate
                            Else 'si on trouve directement une valeur (la mise à jour a été faite)
                            donnee_123 = Cells(ligne, i).Value + Cells(ligne, i + 1).Value + Cells(ligne, i + 2).Value 'on récupère la somme des 3 données dans une variable
                            l_date = Cells(ligne, 1) 'on récupère la date correspondante
 
Verif.Activate
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 10) = "YES" 'on vient remplir YES car la mise à jour a été faite
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 11) = l_date 'on remplit la date correspondante (normalement la date rentrée par l'user vu que la maj est ok
                            Verif.Worksheets(Sheets.Count).Cells(vligne, 12) = donnee_123 'on rentre la valeur des données
 
                        End If
                    Next i
                End If
            Next n
        End If
    Next c
 
    WbSource.Close 'ferme le fichier source
    fichier = Dir 'va vers le fichier suivant à importer
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 
'Une fois que la boucle fonctionne il faudra juste rajouter la recherche pour les 2 autres types de données
'        :=False, Transpose:=False
 
Loop 'recommence la boucle avec le fichier suivant
 
End Sub

02/09/2019, 21h43
eriiic

Code:

Public rdate As String

As string c'est voulu ?
Si tu compares une chaine à une date numérique tu ne trouveras jamais l'égalité.

Il va falloir que tu apprennes le minimum en débogage : mettre un point d'arrêt et contrôler une variable ou une expression.
On risque d'arriver à un point où toi seul pourras contrôler certains éléments. Sinon on va y passer des semaines...
eric
02/09/2019, 21h46
momzoo

En effet c'est une erreur de ma part :oops:, les autres variables de type date ne sont pas déclarées en string (voire vdate et ldate par exemple).
En effet là je les ai enlevé mais je met plein de msgbox pour bien vérifier la cohérence, le problème étant que mes boucles n'arrivent pas au calcul des dites valeurs pour que je les vérifie, ça me renvoie une case vide.

Vois-tu d'autres problèmes de la sorte ?

N'ayant pas le bon pc sous le coude je ferai le run dans la matinée avec d'autres corrections et je revérifierai les variables pour préciser là ou ça bloque exactement.
Je te remercie pour tes réponses en tout cas !
02/09/2019, 22h12
eriiic

Les boucles par elles même ne me paraissent pas incongrues.
C'est pour ça qu'il faut que tu regardes de près les tests. Et là seul un pas à pas en contrôlant les valeurs peut t'éclairer.
eric
03/09/2019, 16h56
momzoo

Citation:

Envoyé par eriiic

Les boucles par elles même ne me paraissent pas incongrues.
C'est pour ça qu'il faut que tu regardes de près les tests. Et là seul un pas à pas en contrôlant les valeurs peut t'éclairer.
eric

Salut Eric !

Alors oui j'ai pu vérifier toutes les variables, et il se trouve que certaines n'étaient pas bonnes car la macro n'était pas dans le bon fichier sur le moment, donc il a fallu que je revérifie bien le déroulement en activant la bonne feuille au bon moment et là tout marche nickel !

Merci pour ton assistance !