Discussion :

[Frantz_B]

Bonjour,
D'habitude je trouve des réponses déjà publiées, mais pour ce cas je fais appel à vos connaissances :
Il s'agit d'optimiser la recopie d'une ligne de valeurs vers 4 colonnes d'une autre feuille.
Actuellement je traite cela avec un index ... mais je vais avoir fini le développement et je souhaite optimiser ce qui marche bien mais lentement.
La structure est la suivante :
Une ligne avec trois cellules qui servent à la numérotation contient ensuite 32 (4 fois 8) informations qui doivent être copiées vers une autre feuille en quatre colonnes de huit cellules.
Voici le code non optimisé dont je vous remercie d'effectuer les améliorations qui vous sembleront judicieuses, d'avance je vous en remercie.
Frantz

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
For i = 0 To 31
        Array_Com(i) = Sheets("P_Comments").Cells(Ligne, i + 4).Value ' Les données commencent à la colonne D dans P_Comment
    Next i
    For i = 0 To 7
' Les quatre séries de commentaires commencent en ligne 26 de P_Rq dans les colonnes B, E, J et M
        Sheets("P_Rq").Cells(i + 26, 2) = Array_Com(i)
        Sheets("P_Rq").Cells(i + 26, 5) = Array_Com(i + 8)
        Sheets("P_Rq").Cells(i + 26, 10) = Array_Com(i + 16)
        Sheets("P_Rq").Cells(i + 26, 13) = Array_Com(i + 24)
    Next i

Merci de votre retour, Frantz

[patricktoulon]

bonjour mis a part différente méthode possible je vois pas trop
car tu n'en dis pas assez long sur tes intentions

exemple tu prends un array de 31 élément et tu te sert que de 4

etc......

[OBO29]

Salut, je n'ai pas bcp d'XP; voici mes pistes pour tes transpositions.
? Ton résultat est toujours au même endroit ? Je ne comprends pas bien l'intérêt...

Test0 : 3'06 = 186 sec
Test3 : 2'47 = 167 sec
Delta = 19 sec
10% de gain

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
 
'Ton code sur 150000 lignes
Sub test_0() '3min06
Dim i As Double
Dim Array_Com(32) As String
Dim debut As Date, temps As Date, fin As Date
debut = Time
Sheets("P_Comments").Select
For ligne = 1 To 150000
    For i = 0 To 31
        Array_Com(i) = Sheets("P_Comments").Cells(ligne, i + 4).Value ' Les données commencent à la colonne D dans P_Comment
    Next i
    For i = 0 To 7 ' Les quatre séries de commentaires commencent en ligne 26 de P_Rq dans les colonnes B, E, J et M
        Sheets("P_Rq").Cells(i + 26, 2) = Array_Com(i)
        Sheets("P_Rq").Cells(i + 26, 5) = Array_Com(i + 8)
        Sheets("P_Rq").Cells(i + 26, 10) = Array_Com(i + 16)
        Sheets("P_Rq").Cells(i + 26, 13) = Array_Com(i + 24)
    Next i
Next ligne
fin = Time
temps = fin - debut
MsgBox ("C'est fini !" & Chr(10) & "temps de traitement " & temps)
End Sub
 
'test1 :
'Application.Calculation
'Application.ScreenUpdating
Sub test_1() '2min54
Dim i As Double
Dim Array_Com(32) As String
Dim debut As Date, temps As Date, fin As Date
debut = Time
Application.Calculation = xlCalculationManual   'Ajout test1
Application.ScreenUpdating = False   'Ajout test1
Sheets("P_Comments").Select
For ligne = 1 To 150000
    For i = 0 To 31
        Array_Com(i) = Sheets("P_Comments").Cells(ligne, i + 4).Value ' Les données commencent à la colonne D dans P_Comment
    Next i
    For i = 0 To 7 ' Les quatre séries de commentaires commencent en ligne 26 de P_Rq dans les colonnes B, E, J et M
        Sheets("P_Rq").Cells(i + 26, 2) = Array_Com(i)
        Sheets("P_Rq").Cells(i + 26, 5) = Array_Com(i + 8)
        Sheets("P_Rq").Cells(i + 26, 10) = Array_Com(i + 16)
        Sheets("P_Rq").Cells(i + 26, 13) = Array_Com(i + 24)
    Next i
Next ligne
Application.ScreenUpdating = True   'Ajout test1
Application.Calculation = xlCalculationAutomatic   'Ajout test1
fin = Time
temps = fin - debut
MsgBox ("C'est fini !" & Chr(10) & "temps de traitement " & temps)
End Sub
 
'test2 :
'With
'supression du +26 répétitif
Sub test_2() '2min50
Dim i As Double, j As Integer
Dim Array_Com(32) As String
Dim debut As Date, temps As Date, fin As Date
debut = Time
Application.Calculation = xlCalculationManual   'Ajout test1
Application.ScreenUpdating = False   'Ajout test1
Sheets("P_Comments").Select
For ligne = 1 To 150000
    For i = 0 To 31
        Array_Com(i) = Sheets("P_Comments").Cells(ligne, i + 4).Value ' Les données commencent à la colonne D dans P_Comment
    Next i
    For i = 0 To 7 ' Les quatre séries de commentaires commencent en ligne 26 de P_Rq dans les colonnes B, E, J et M
        j = i + 26
        With Sheets("P_Rq")
            .Cells(j, 2) = Array_Com(i)
            .Cells(j, 5) = Array_Com(i + 8)
            .Cells(j, 10) = Array_Com(i + 16)
            .Cells(j, 13) = Array_Com(i + 24)
        End With
    Next i
Next ligne
Application.ScreenUpdating = True   'Ajout test1
Application.Calculation = xlCalculationAutomatic   'Ajout test1
fin = Time
temps = fin - debut
MsgBox ("C'est fini !" & Chr(10) & "temps de traitement " & temps)
End Sub
 
'test3 :
'supression de la copie intermédiaire
Sub test_3() '2min47
Dim i As Double, j As Integer
Dim debut As Date, temps As Date, fin As Date
debut = Time
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Sheets("P_Comments").Select
For ligne = 1 To 150000
    ' Les données commencent à la colonne D dans P_Comment
    ' Les quatre séries de commentaires commencent en ligne 26 de P_Rq dans les colonnes B, E, J et M
    For i = 0 To 7
        j = i + 26
        With Sheets("P_Rq")
            .Cells(j, 2) = Sheets("P_Comments").Cells(ligne, i + 4).Value 'Array_Com(i)
            .Cells(j, 5) = Sheets("P_Comments").Cells(ligne, i + 12).Value 'Array_Com(i + 8)
            .Cells(j, 10) = Sheets("P_Comments").Cells(ligne, i + 20).Value 'Array_Com(i + 16)
            .Cells(j, 13) = Sheets("P_Comments").Cells(ligne, i + 28).Value 'Array_Com(i + 24)
        End With
    Next i
Next ligne
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
fin = Time
temps = fin - debut
MsgBox ("C'est fini !" & Chr(10) & "temps de traitement " & temps)
End Sub
 
'test4 :
'fonction de transposition Excel
Sub test_4() 'A oublier dans l'état
Dim debut As Date, temps As Date, fin As Date
debut = Time
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Sheets("P_Comments").Select
For ligne = 1 To 150000
    ' Les données commencent à la colonne D dans P_Comment
    ' Les quatre séries de commentaires commencent en ligne 26 de P_Rq dans les colonnes B, E, J et M
    Sheets("P_Comments").Range(Cells(1, 4), Cells(1, 11)).Copy
    Sheets("P_Rq").Cells(26, 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("P_Comments").Range(Cells(1, 12), Cells(1, 19)).Copy
    Sheets("P_Rq").Cells(26, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("P_Comments").Range(Cells(1, 20), Cells(1, 27)).Copy
    Sheets("P_Rq").Cells(26, 10).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("P_Comments").Range(Cells(1, 28), Cells(1, 35)).Copy
    Sheets("P_Rq").Cells(26, 13).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Next ligne
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
fin = Time
temps = fin - debut
MsgBox ("C'est fini !" & Chr(10) & "temps de traitement " & temps)
End Sub

[Frantz_B]

bonjour mis a part différente méthode possible je vois pas trop
car tu n'en dis pas assez long sur tes intentions

exemple tu prends un array de 31 élément et tu te sert que de 4

etc......

Non les quatre lignes font bien les quatre colonnes et cela huit fois, donc l'array est complètement utilisé.
Ceci dit j'ai bien dit que ce code fonctionne mais est lent.
Mes intentions étaient donc de chercher à optimiser. Du genre un "coller" réalisé d'une façon globale pour quatre tranches de huit ...
J'espère avoir éclairé ma demande d'optimisation.

[Frantz_B]

Salut, je n'ai pas bcp d'XP; voici mes pistes pour tes transpositions.
? Ton résultat est toujours au même endroit ? Je ne comprends pas bien l'intérêt...

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
'test4 :
'fonction de transposition Excel
Sub test_4() 'A oublier dans l'état
Dim debut As Date, temps As Date, fin As Date
debut = Time
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Sheets("P_Comments").Select
For ligne = 1 To 150000
    ' Les données commencent à la colonne D dans P_Comment
    ' Les quatre séries de commentaires commencent en ligne 26 de P_Rq dans les colonnes B, E, J et M
    Sheets("P_Comments").Range(Cells(1, 4), Cells(1, 11)).Copy
    Sheets("P_Rq").Cells(26, 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("P_Comments").Range(Cells(1, 12), Cells(1, 19)).Copy
    Sheets("P_Rq").Cells(26, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("P_Comments").Range(Cells(1, 20), Cells(1, 27)).Copy
    Sheets("P_Rq").Cells(26, 10).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("P_Comments").Range(Cells(1, 28), Cells(1, 35)).Copy
    Sheets("P_Rq").Cells(26, 13).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Next ligne
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
fin = Time
temps = fin - debut
MsgBox ("C'est fini !" & Chr(10) & "temps de traitement " & temps)
End Sub

Je n'ai pas encore testé le test 4 reproduit ci-dessus mais cela ressemble bigrement à ce que j'aurais aimé savoir faire seul,.
Je reviens ici avec les performances, mais au moins en terme de code cela ressemble à un copier / coller / transposé que l'on fait à la main !
PS : Non le résultat change car, comme je le disais dans la question initiale la copie varie avec l'index et on a donc un résultat différent sur une même feuille de synthèse.