Bonjour
J´ai créé un ptit programme permettant de transférer des données dans différentes pages en fonction de la valeur d´une cellule et dans chaque pages de les mettre dans une plage de colonnes (7 colonnes ici) en fonction de la valeur d´une autre cellule. Le pb est qu avec ma méthode les données se retrouve à la meme ligne que dans la page initiale, il y a donc une multitude de cellules vides, j´ai donc ajouter qq choses pour supprimer ttes les cellules vides mais ce programme est vraiment long. Il se peut que j ai plus de 1000 lignes de temps en temps et le programme calcule pendant plus de 5 min.
Mon programme :
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
Sub DeleteWarning()
 
 
Dim DerLig As Long, l As Long, i As Long, k As Long, ib As Long, kb As Long, ih As Long, kh As Long, ip As Long, kp As Long, LastRowb As Long, LastRowh As Long, LastRowp As Long, h As Long, b As Long, p As Long
 
 
Dim CL As Range
 
 ' Delete all rows where there are  a warning which not stop the turbine
With Sheets(1)
    DerLig = .Range("A65000").End(xlUp).Row
    For l = DerLig To 3 Step -1
        If .Cells(l, 11).Value = 0 Then
            If CL Is Nothing Then
                Set CL = .Cells(l, 11)
            Else
                Set CL = Union(.Cells(l, 11), CL)
            End If
        End If
    Next l
    Set CL = CL.EntireRow
    CL.Delete
End With
 
'Put data in the correst sheet and in the correct turnines column
DerLig = Sheets("ExportedData").Range("A65000").End(xlUp).Row
 
For i = DerLig To 3 Step -1
    For k = 1 To 6
        If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(2, 3).Value Then Sheets("Bruck").Cells(i, k).Value = Sheets("ExportedData").Cells(i, k).Value
            If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(3, 3).Value Then Sheets("Bruck").Cells(i, k + 7).Value = Sheets("ExportedData").Cells(i, k).Value
                If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(4, 3).Value Then Sheets("Bruck").Cells(i, k + 14).Value = Sheets("ExportedData").Cells(i, k).Value
                    If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(5, 3).Value Then Sheets("Bruck").Cells(i, k + 21).Value = Sheets("ExportedData").Cells(i, k).Value
                        If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(6, 3).Value Then Sheets("Bruck").Cells(i, k + 28).Value = Sheets("ExportedData").Cells(i, k).Value
                            If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(2, 4).Value Then Sheets("Hollerm").Cells(i, k).Value = Sheets("ExportedData").Cells(i, k).Value
                                If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(3, 4).Value Then Sheets("Hollerm").Cells(i, k + 7).Value = Sheets("ExportedData").Cells(i, k).Value
                                    If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(4, 4).Value Then Sheets("Hollerm").Cells(i, k + 14).Value = Sheets("ExportedData").Cells(i, k).Value
                                        If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(5, 4).Value Then Sheets("Hollerm").Cells(i, k + 21).Value = Sheets("ExportedData").Cells(i, k).Value
                                            If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(6, 4).Value Then Sheets("Hollerm").Cells(i, k + 28).Value = Sheets("ExportedData").Cells(i, k).Value
                                                If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(7, 4).Value Then Sheets("Hollerm").Cells(i, k + 35).Value = Sheets("ExportedData").Cells(i, k).Value
                                                    If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(8, 4).Value Then Sheets("Hollerm").Cells(i, k + 42).Value = Sheets("ExportedData").Cells(i, k).Value
                                                        If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(9, 4).Value Then Sheets("Hollerm").Cells(i, k + 49).Value = Sheets("ExportedData").Cells(i, k).Value
                                                            If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(10, 4).Value Then Sheets("Hollerm").Cells(i, k + 56).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(2, 5).Value Then Sheets("Petronell").Cells(i, k).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                    If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(3, 5).Value Then Sheets("Petronell").Cells(i, k + 7).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                        If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(4, 5).Value Then Sheets("Petronell").Cells(i, k + 14).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                            If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(5, 5).Value Then Sheets("Petronell").Cells(i, k + 21).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                                If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(6, 5).Value Then Sheets("Petronell").Cells(i, k + 28).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                                    If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(7, 5).Value Then Sheets("Petronell").Cells(i, k + 35).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                                        If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(8, 5).Value Then Sheets("Petronell").Cells(i, k + 42).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                                            If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(9, 5).Value Then Sheets("Petronell").Cells(i, k + 49).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                                                If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(10, 5).Value Then Sheets("Petronell").Cells(i, k + 56).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                                                    If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(11, 5).Value Then Sheets("Petronell").Cells(i, k + 63).Value = Sheets("ExportedData").Cells(i, k).Value
                                                                                                        If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(12, 5).Value Then Sheets("Petronell").Cells(i, k + 70).Value = Sheets("ExportedData").Cells(i, k).Value
 
    Next k
Next i
 
'Delete all cells where there are nothing inside
With Sheets("Bruck")
For b = 1 To 35 Step 7
 
    LastRowb = Sheets("bruck").Cells(65000, b).End(xlUp).Row
 
    For ib = LastRowb To 3 Step -1
        For kb = 1 To 35
 
        If .Cells(ib, kb).Value = "" Then .Cells(ib, kb).Delete xlShiftUp
 
        Next kb
    Next ib
Next b
End With
 
With Sheets("petronell")
For p = 1 To 63 Step 7
LastRowp = Sheets("petronell").Cells(65000, p).End(xlUp).Row
    For ip = LastRowp To 3 Step -1
        For kp = 1 To 77
 
            If .Cells(ip, kp).Value = "" Then .Cells(ip, kp).Delete xlShiftUp
 
        Next kp
    Next ip
Next p
End With
 
With Sheets("Hollerm")
 
For h = 1 To 63 Step 7
LastRowh = Sheets("Hollerm").Cells(65000, h).End(xlUp).Row
    For ih = LastRowh To 3 Step -1
        For kh = 1 To 63
 
            If .Cells(ih, kh).Value = "" Then .Cells(ih, kh).Delete xlShiftUp
 
        Next kh
    Next ih
Next h
End With
 
 
 
 
 
End Sub
Voilà si qq a une solution plus rapide je suis preneur.

Merci