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
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
| Sub RechercherEtRécupérer()
Dim wsParam As Worksheet
Dim wsBalanceN As Worksheet
Dim wsBalanceN_1 As Worksheet
Dim numCompte As String
Dim resultD As Variant
Dim resultF As Variant
Dim compteRecherche As String
Dim found As Boolean
Dim balanceRow As Long
Dim i As Long
Dim lastRow As Long
Dim valuesL As Variant
Dim valuesM As Variant
Dim valueR As Variant
Dim indexValue As Variant
' Définir la feuille de travail (Paramétrage des leads)
Set wsParam = ThisWorkbook.Sheets("Paramétrage des leads")
' Définir les feuilles de résultats (Balance N et Balance N-1)
Set wsBalanceN = ThisWorkbook.Sheets("Balance N")
Set wsBalanceN_1 = ThisWorkbook.Sheets("Balance N-1")
' Trouver la dernière ligne non vide dans la colonne A de la feuille "Balance N" en excluant la ligne des totaux
lastRow = wsBalanceN.Cells(wsBalanceN.Rows.Count, "A").End(xlUp).Row - 1
' Parcourir toutes les lignes non vides
For i = 2 To lastRow
numCompte = wsBalanceN.Cells(i, 1).Value ' Lire le numéro de compte depuis la colonne A de "Balance N"
' Initialiser les variables de résultats
resultD = "Non trouvé"
resultF = "Non trouvé"
found = False
' Étape 1 : Recherche avec les 3 premiers caractères
compteRecherche = Left(numCompte, 3)
resultD = RechercheCompte(compteRecherche, wsParam, 4) ' Recherche pour la colonne D (ancien résultat)
resultF = RechercheCompte(compteRecherche, wsParam, 6) ' Recherche pour la colonne F (nouveau résultat)
If resultD <> "Non trouvé" Or resultF <> "Non trouvé" Then
' Placer les résultats dans les colonnes L et M de Balance N et Balance N-1
balanceRow = TrouverLigne(wsBalanceN, numCompte)
If balanceRow > 0 Then
If resultD <> "Non trouvé" Then
wsBalanceN.Cells(balanceRow, 12).Value = resultD ' Colonne L est la 12ème colonne
End If
If resultF <> "Non trouvé" Then
wsBalanceN.Cells(balanceRow, 13).Value = resultF ' Colonne M est la 13ème colonne
End If
End If
balanceRow = TrouverLigne(wsBalanceN_1, numCompte)
If balanceRow > 0 Then
If resultD <> "Non trouvé" Then
wsBalanceN_1.Cells(balanceRow, 12).Value = resultD
End If
If resultF <> "Non trouvé" Then
wsBalanceN_1.Cells(balanceRow, 13).Value = resultF
End If
End If
found = True
End If
' Si non trouvé, étape 2 : Recherche avec les 2 premiers caractères
If Not found Then
compteRecherche = Left(numCompte, 2)
resultD = RechercheCompte(compteRecherche, wsParam, 4)
resultF = RechercheCompte(compteRecherche, wsParam, 6)
If resultD <> "Non trouvé" Or resultF <> "Non trouvé" Then
' Placer les résultats dans les colonnes L et M de Balance N et Balance N-1
balanceRow = TrouverLigne(wsBalanceN, numCompte)
If balanceRow > 0 Then
If resultD <> "Non trouvé" Then
wsBalanceN.Cells(balanceRow, 12).Value = resultD
End If
If resultF <> "Non trouvé" Then
wsBalanceN.Cells(balanceRow, 13).Value = resultF
End If
End If
balanceRow = TrouverLigne(wsBalanceN_1, numCompte)
If balanceRow > 0 Then
If resultD <> "Non trouvé" Then
wsBalanceN_1.Cells(balanceRow, 12).Value = resultD
End If
If resultF <> "Non trouvé" Then
wsBalanceN_1.Cells(balanceRow, 13).Value = resultF
End If
End If
found = True
End If
End If
' Si non trouvé, étape 3 : Recherche avec le 1er caractère
If Not found Then
compteRecherche = Left(numCompte, 1)
resultD = RechercheCompte(compteRecherche, wsParam, 4)
resultF = RechercheCompte(compteRecherche, wsParam, 6)
If resultD <> "Non trouvé" Or resultF <> "Non trouvé" Then
' Placer les résultats dans les colonnes L et M de Balance N et Balance N-1
balanceRow = TrouverLigne(wsBalanceN, numCompte)
If balanceRow > 0 Then
If resultD <> "Non trouvé" Then
wsBalanceN.Cells(balanceRow, 12).Value = resultD
End If
If resultF <> "Non trouvé" Then
wsBalanceN.Cells(balanceRow, 13).Value = resultF
End If
End If
balanceRow = TrouverLigne(wsBalanceN_1, numCompte)
If balanceRow > 0 Then
If resultD <> "Non trouvé" Then
wsBalanceN_1.Cells(balanceRow, 12).Value = resultD
End If
If resultF <> "Non trouvé" Then
wsBalanceN_1.Cells(balanceRow, 13).Value = resultF
End If
End If
found = True
End If
End If
' Si toujours pas trouvé, indiquer "Non trouvé"
If Not found Then
balanceRow = TrouverLigne(wsBalanceN, numCompte)
If balanceRow > 0 Then
wsBalanceN.Cells(balanceRow, 12).Value = "Non trouvé"
wsBalanceN.Cells(balanceRow, 13).Value = "Non trouvé"
End If
balanceRow = TrouverLigne(wsBalanceN_1, numCompte)
If balanceRow > 0 Then
wsBalanceN_1.Cells(balanceRow, 12).Value = "Non trouvé"
wsBalanceN_1.Cells(balanceRow, 13).Value = "Non trouvé"
End If
End If
' Éclatement des valeurs des colonnes L et M en fonction du slash (/) dans Balance N
valuesL = Split(wsBalanceN.Cells(balanceRow, 12).Value, "/")
If UBound(valuesL) >= 0 Then wsBalanceN.Cells(balanceRow, 14).Value = valuesL(0) ' Colonne N
If UBound(valuesL) >= 1 Then wsBalanceN.Cells(balanceRow, 15).Value = valuesL(1) ' Colonne O
valuesM = Split(wsBalanceN.Cells(balanceRow, 13).Value, "/")
If UBound(valuesM) >= 0 Then wsBalanceN.Cells(balanceRow, 16).Value = valuesM(0) ' Colonne P
If UBound(valuesM) >= 1 Then wsBalanceN.Cells(balanceRow, 17).Value = valuesM(1) ' Colonne Q
' Éclatement des valeurs des colonnes L et M en fonction du slash (/) dans Balance N-1
valuesL = Split(wsBalanceN_1.Cells(balanceRow, 12).Value, "/")
If UBound(valuesL) >= 0 Then wsBalanceN_1.Cells(balanceRow, 14).Value = valuesL(0) ' Colonne N
If UBound(valuesL) >= 1 Then wsBalanceN_1.Cells(balanceRow, 15).Value = valuesL(1) ' Colonne O
valuesM = Split(wsBalanceN_1.Cells(balanceRow, 13).Value, "/")
If UBound(valuesM) >= 0 Then wsBalanceN_1.Cells(balanceRow, 16).Value = valuesM(0) ' Colonne P
If UBound(valuesM) >= 1 Then wsBalanceN_1.Cells(balanceRow, 17).Value = valuesM(1) ' Colonne Q
' Calculer la valeur de la colonne R pour Balance N et Balance N-1
' Calcul pour Balance N
indexValue = Application.WorksheetFunction.Index(wsParam.Range("B6:B156"), Application.WorksheetFunction.Match(wsBalanceN.Cells(balanceRow, 12).Value, wsParam.Range("D6:D156"), 0))
wsBalanceN.Cells(balanceRow, 18).Value = indexValue
' Calcul pour Balance N-1
indexValue = Application.WorksheetFunction.Index(wsParam.Range("B6:B156"), Application.WorksheetFunction.Match(wsBalanceN_1.Cells(balanceRow, 12).Value, wsParam.Range("D6:D156"), 0))
wsBalanceN_1.Cells(balanceRow, 18).Value = indexValue
Next i
Call RemplirBalanceAvecFeuilles
End Sub
' Fonction pour rechercher le compte dans la matrice et retourner la valeur de la colonne spécifiée
Function RechercheCompte(compte As String, ws As Worksheet, colonne As Long) As Variant
Dim rng As Range
Dim cell As Range
Dim result As String
' Définir la plage de recherche dans la colonne des comptes (colonne A)
Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
' Recherche du compte en fonction des premiers caractères
For Each cell In rng
If Left(cell.Value, Len(compte)) = compte Then ' Comparer les premiers caractères
result = cell.Offset(0, colonne - 1).Value ' L'offset est ajusté pour accéder à la colonne demandée
RechercheCompte = result
Exit Function
End If
Next cell
RechercheCompte = "Non trouvé"
End Function
' Fonction pour trouver la ligne correspondante dans une feuille donnée (Balance N ou Balance N-1)
Function TrouverLigne(ws As Worksheet, numCompte As String) As Long
Dim rng As Range
Dim foundCell As Range
' Définir la plage de recherche dans la colonne des comptes (colonne A)
Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
' Recherche du compte dans la colonne A
Set foundCell = rng.Find(What:=numCompte, LookIn:=xlValues, LookAt:=xlWhole)
' Retourner le numéro de ligne si trouvé
If Not foundCell Is Nothing Then
TrouverLigne = foundCell.Row
Else
TrouverLigne = -1 ' Compte non trouvé
End If
End Function
Sub RemplirBalanceAvecFeuilles()
Dim wsBalanceN As Worksheet
Dim wsBalanceN_1 As Worksheet
Dim wsDest As Worksheet
Dim i As Long
Dim lastRow As Long
Dim lastRowDest As Long
Dim feuilleNom As String
Dim montantG As Double
Dim montantN As Double
Dim difference As Double
Dim compteRecherche As String
Dim cell As Range
Dim Derlign As Long
' Définir les feuilles de travail (Balance N et Balance N-1)
Set wsBalanceN = ThisWorkbook.Sheets("Balance N")
Set wsBalanceN_1 = ThisWorkbook.Sheets("Balance N-1")
' Trouver la dernière ligne non vide de la colonne A dans la feuille Balance N
lastRow = wsBalanceN.Cells(wsBalanceN.Rows.Count, "A").End(xlUp).Row
' Commencer à partir de la ligne 2 et parcourir jusqu'à la dernière ligne
For i = 2 To lastRow
' Vérifier si la colonne K est positive ou égale à 0, puis récupérer le nom de la feuille
If wsBalanceN.Cells(i, 11).Value >= 0 Then ' Si K >= 0, on prend N
feuilleNom = wsBalanceN.Cells(i, 14).Value ' Colonne N
Else ' Si K est négatif, on prend O
feuilleNom = wsBalanceN.Cells(i, 15).Value ' Colonne O
End If
' Vérifier si la feuille nommée existe dans le classeur
If feuilleNom <> "" Then
On Error Resume Next
Set wsDest = ThisWorkbook.Sheets(feuilleNom)
On Error GoTo 0
If wsDest Is Nothing Then
MsgBox "La feuille " & feuilleNom & " n'existe pas.", vbCritical
GoTo NextIteration ' Passer à la prochaine itération
End If
Else
MsgBox "La cellule contenant le nom de la feuille est vide à la ligne " & i, vbCritical
GoTo NextIteration ' Passer à la prochaine itération
End If
' Définir la feuille de destination
On Error Resume Next
Set wsDest = ThisWorkbook.Sheets(feuilleNom)
On Error GoTo HandleError
If wsDest Is Nothing Then
MsgBox "La feuille '" & feuilleNom & "' n'existe pas. (Cellule : " & wsBalanceN.Cells(i, 1).Address & " dans Balance N)", vbCritical
GoTo NextIteration
End If
Exit Sub
HandleError:
MsgBox "Erreur lors de l'accès à la feuille. Nom : " & feuilleNom & " (Cellule : " & wsBalanceN.Cells(i, 1).Address & ")"
GoTo NextIteration
' Vérifier si la feuille de destination existe
If wsDest Is Nothing Then
MsgBox "La feuille " & feuilleNom & " n'existe pas.", vbCritical
GoTo NextIteration
End If
' Effacer les anciennes données dans la plage A11:L500
wsDest.Range("A11:L500").Delete shift:=True
wsDest.Range("A10").Value = "N° Compte"
wsDest.Range("J10").Value = "J10"
wsDest.Range("K10").Value = "K10"
' Vérifier si la valeur dans N ou O est valide
If wsBalanceN.Cells(i, 14).Value <> "" And IsError(Evaluate("ISREF('" & wsBalanceN.Cells(i, 14).Value & "'!A1)")) Then
MsgBox "La feuille nommée " & wsBalanceN.Cells(i, 14).Value & " n'existe pas.", vbCritical
GoTo NextIteration
End If
' Trouver la dernière ligne vide dans la feuille de destination à partir de la ligne 10
lastRowDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
If lastRowDest < 10 Then lastRowDest = 10 ' S'assurer que la ligne 10 est la première ligne des données
' Remplir les données dans la feuille de destination
wsDest.Cells(lastRowDest, 1).Value = wsBalanceN.Cells(i, 1).Value ' Colonne A (Numéro de compte)
wsDest.Cells(lastRowDest, 2).Value = wsBalanceN.Cells(i, 2).Value ' Colonne R (Valeur correspondante)
wsDest.Cells(lastRowDest, 3).Value = wsBalanceN.Cells(i, 11).Value ' Colonne K
' Calculer les autres colonnes
wsDest.Cells(lastRowDest, 4).Value = "" ' Colonne D vide
wsDest.Cells(lastRowDest, 5).Value = wsDest.Cells(lastRowDest, 3).Value + wsDest.Cells(lastRowDest, 4).Value ' Colonne E (C+D)
wsDest.Cells(lastRowDest, 6).Value = "" ' Colonne F vide
' Recherche du montant dans la Balance N-1 basé sur la colonne A (Numéro de compte)
compteRecherche = wsBalanceN.Cells(i, 1).Value ' Numéro de compte dans Balance N
' Rechercher le compte dans la Balance N-1 (colonne A)
Set cell = wsBalanceN_1.Range("A2:A" & wsBalanceN_1.Cells(wsBalanceN_1.Rows.Count, "A").End(xlUp).Row).Find(compteRecherche, LookIn:=xlValues, LookAt:=xlWhole)
' Si trouvé, récupérer la valeur correspondante de la colonne K de la Balance N-1
If Not cell Is Nothing Then
montantG = cell.Offset(0, 10).Value ' Colonne K de Balance N-1 (colonne A + 10 = colonne K)
wsDest.Cells(lastRowDest, 7).Value = montantG ' Colonne G
Else
wsDest.Cells(lastRowDest, 7).Value = "Non trouvé" ' Si non trouvé, afficher "Non trouvé"
End If
' Calculer la différence entre E et G (H = E - G)
montantN = wsDest.Cells(lastRowDest, 5).Value
difference = montantN - montantG
wsDest.Cells(lastRowDest, 8).Value = difference ' Colonne H
' Utiliser la fonction SI pour gérer l'erreur de division par zéro pour le taux d'erreur (Colonne I)
If montantG <> 0 Then
wsDest.Cells(lastRowDest, 9).Value = difference / montantG ' Colonne I (H/G)
Else
wsDest.Cells(lastRowDest, 9).Value = "Erreur" ' Erreur si G = 0
End If
NextIteration:
Next i
' Remplir la colonne L avec les valeurs selon la logique définie
For Derlign = 11 To lastRowDest
' Vérifier la condition sur la colonne K de la feuille Balance N
If wsBalanceN.Cells(i, 11).Value >= 0 Then
' Si K >= 0, prendre la valeur dans la colonne P (Balance N)
wsDest.Cells(Derlign, 12).Value = wsBalanceN.Cells(i, 16).Value ' Colonne P de Balance N
Else
' Si K < 0, prendre la valeur dans la colonne Q (Balance N)
wsDest.Cells(Derlign, 12).Value = wsBalanceN.Cells(i, 17).Value ' Colonne Q de Balance N
End If
Next Derlign
' Tri des données avant les sous-totaux par la colonne L (colonne 12)
wsDest.Sort.SortFields.Clear
wsDest.Sort.SortFields.Add key:=wsDest.Range("L11:L" & lastRowDest), Order:=xlAscending, DataOption:=xlSortNormal
wsDest.Sort.SetRange wsDest.Range("A10:L" & lastRowDest)
wsDest.Sort.Header = xlYes
wsDest.Sort.Apply
' Appliquer les sous-totaux, cette fois en groupant par la colonne L
wsDest.Range("A10:L" & lastRowDest).Subtotal GroupBy:=12, Function:=xlSum, TotalList:=Array(5, 7, 8, 9), Replace:=True, PageBreaks:=True ' Active les sauts de page pour un formatage visuel avec la ligne grisonnée
End Sub |
automatiser la gestion des balances financières
Répondre avec citation
Partager