Discussion :

[Kormondre]

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.

[Invité]

et bien enlève :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Next i
 
'on complète ensuite les lignes vides par les services de la template
 
For i = 28 To lastrow

et tu n'aura que 1 boucle

[EngueEngue]

Peut aider..

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Application.ScreenUpdating = False
'code
Application.ScreenUpdating = True

[Kormondre]

j'ai mis le code complet au cas ou

[Kormondre]

et bien enlève :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Next i
 
'on complète ensuite les lignes vides par les services de la template
 
For i = 28 To lastrow

et tu n'aura que 1 boucle

j'ai essayé cela ne réduit pas le temps de calcul

[EngueEngue]

Le code est propre. Tu ne peux pas optimiser en nombre d'opérations par contre tu peux sûrement faire des opérations moins coûteuses en temps marchine.. Que font ces lignes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
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))"

[Kormondre]

Ces lignes vont chercher dans la db analytique la ligne portant la clé de la tâche lié à la facture (type_document + numéro document + catégorie tache + numéro tache) et renvoient le contenu d'une certaine colonne lié à cet clé unique (un recherche v en fait)

[EngueEngue]

Et bien c'est de ce coté là qu'il faut regarder pour optimiser, il sera plus rapide de faire autre chose que des formules.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Application.Calculation = xlCalculationManual
'code
Application.Calculation = xlCalculationAutomatic

Si tu veux que je jette un oeuil à tes formules et que les datas ne sont pas confidentielles poste les

[Invité]

et bien enlève :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Next i
 
'on complète ensuite les lignes vides par les services de la template
 
For i = 28 To lastrow

et tu n'aura que 1 boucle

Bonjour,

Je ne comprends pas bien ce fichier, mais je pense qu'il s'agit d'une recherche faite entre la ligne 28 et la dernière ligne (lastrow = 65536).

Il semble que les 2 boucles soient différentes:
Boucle 1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

' on importe ici les lignes contenus dans la facture voulue.

Boucle 2:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

'on complète ensuite les lignes vides par les services de la template

En attendant, je proposerai de mettre un timer entre chaques étapes pour déterminer dans un 1er temps quels sont les étapes qui prennent un temps négligeable (sur lesquels il sera inutile d'agir) et non négligeable (celles sur lesquels il faudra agir).

t = Timer au début,

Après la 1ère boucle:
[A1] = Timer - t

Après la 2ème boucle:
[A2] = Timer - t

A la fin de la Macro:
[A3] = Timer - t

La cellule A1:A3 doivent être libre.

Ça permettra déjà d'aller dans la bonne direction pour la modife du code.


A+


EDIT:
Tu écris des formules enfaite.

- Soit il faut travailler avec des tableaux pour écrire en mémoire vive, puis ensuite balancer tout le tableau contenu en mémoire sur une plage de cellule en 1 seule fois.

- Soit, tu utilise la méthode Autofill, pour faire glisser les formules d'une cellule à une autre (méthode efficace). Regarde un exemple ici http://www.developpez.net/forums/d13...a/#post7368176

[Kormondre]

merci pour ta réponse. Est-ce que tu aurais un lien pour apprendre comment utiliser les tableaux en mémoire car je n'ai jamais appris cette technique. Pour la deuxième méthode je ne peux pas utiliser autofill car je dois appliquer la formule seulement sur certaines lignes.

[EngueEngue]

Un tableau est un array de variables stockées en mémoire:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Dim Tableau(28 to 65536) as integer
 
Tableau(28)=1

Pour un tableau d'entiers d'une dimension.

[Invité]

Salut,

Juste pour dire, chez moi, le GetTickCount64 indique 0.063 s après l'étape de l'application autofill. Les formules sont enfaite glissé de haut en bas dans un temps totalement négligeable.
Seul l'opération de calcul prendra du temps, mais tu ne pourras pas agir sur ce temps de calcul. Enfaite si, je me suis apperçu que la fonction Range().Calculate ne donnait pas le même temps de calcul que la fonction Calculate tout court.
Une grosse partie de l'explication provient du fait qu'avec Application.Calculate tout court, C'est Excel qui gère le calcul et peut utiliser les différent coeurs d'execution du processeur. Avec Un CPU 4 coeurs, les 4 coeurs sont chargé à 100%. Avec un range.calculate, le multitreading n'est pas permis et seul 25% d'utilisation CPU sont utilisé. Il y a aussi le gèle de l'affichage et d'autres options qui sont inséré avec l'option Application.Calculate tel la possibilité d'arréter le calcul en court de route ou l'affichage du pourcentage en temps réel des calculs effectués depuis le début etc...
Mais globalement, la technique du Autofill + Calculate est la plus rapide de toutes les solutions confondu (y compris le travail en mémoire en travaillant avec les tableaux).