Bonjour,

J'ai un problème de boucle...

j'ai une feuille excel qui sert à rédiger des factures et à mettre à jour une base de donnée. Il y a une template sur cette feuille. J'ai créé un code qui permet d'importer les anciennes factures dans cette template à partir d'une base de donnée dite analytique. Les attributs de cette template sont : Description service, nombre d'unité de temps, unité de temps, tarif horaire, total htva.

La logique que j'ai suivi pour importer une ancienne facture est la suivante:

2 étapes:

1) injecter une formule INDEX dans chaque cellule de chaque ligne qui va aller chercher le bon attribut dans la base de donnée analytique à partir d'un identifiant unique "catégorie tâche + numéro tâche"

2) si une cellule tache est vide, cela veut dire que ce service n'a pas été utilisé pour cette facture bien précise. Je veux néanmoins compléter cette cellule avec un service provenant de la template au cas ou il s'agit d'une facture ou un devis qui doit être modifiée

Le problème est qu'il y a deux boucles if pour traiter ces deux requètes et cela prend plus ou moins 15 secondes pour les traiter.

Comment faire pour optimiser le code dans ce cas la ?

la condition If Workbooks("template_facture-devis.xlsm").Worksheets("input").Cells(i, 3).Value > 0 me dit si une ligne de la feuille input correspond bien à un service/tache


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
 
 
Sub import_document()
 
Dim lastrow As Long
Dim i As Long
 
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With
 
lastrow = Workbooks("template_facture-devis.xlsm").Worksheets("input").Range("C65536").End(xlUp).Row
 
'd'abord on copie les valeurs des colonnes B et colonnes C (pour éviter la référence circulaire)
 
Sheets("INPUT").Range("B25:C" & lastrow).Value = Sheets("INPUT").Range("B25:C" & lastrow).Value
 
'ensuite on applique une formule index aux colonnes D, G, H, I si et seulement si la colonne C est plus grande que 0 (ligne de tâche)
 
' on importe ici les lignes contenus dans la facture voulue.
 
For i = 28 To lastrow
 
If Workbooks("template_facture-devis.xlsm").Worksheets("input").Cells(i, 3).Value > 0 Then
 
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("D" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),8)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),8))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("G" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-5]&RC[-4],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),9)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-5]&RC[-4],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),9))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("H" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),10)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),10))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("I" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),11)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),11))"
 
End If
 
Next i
 
'on complète ensuite les lignes vides par les services de la template
 
For i = 28 To lastrow
 
If Workbooks("template_facture-devis.xlsm").Worksheets("input").Cells(i, 4).Value = "" And Workbooks("template_facture-devis.xlsm").Worksheets("input").Cells(i, 3).Value > 0 Then
 
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("D" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),4)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),4))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("G" & i).Value = "0"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("H" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),6)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),5))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("I" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),7)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),6))"
 
 
End If
 
Next i
 
 
'ensuite on copie colonnes toutes les cellules des colonnes D à I
 
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("D25:I" & lastrow).Value = Sheets("INPUT").Range("D25:I" & lastrow).Value
 
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
 
End Sub
Je ne sais pas si c'est très clair. Merci d'avance pour vos conseils.