Je suspecte un probleme de type

**darkwall_37** · 11/10/2012, 20h28

Bonjour,

Dans un if je compare des valeurs. Cependant, le programme ne rentre jamais dans la boucle bien que les valeurs affichées soient égales.

J'ai mis un point d'arrêt sur la ligne en question et les valeurs sont bien égales et le programme devrait bien entrer dans le if.

Le programme fonctionnait à la base. Je l'ai repris pour améliorer le temps de traitement car je tapais toujours directement dans les cellules.

Là je pré-charge dans un tableau chaque feuille. Tableaux de variant au passage.

Alors qu'avant je comparais directement les cellules qui étaient de mêmes types. C'est la seule différence que je vois avant le remaniement du code.

Voici le code de la sub qui pose problème

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
Private Sub computeData(sheets() As stSheet, period As Type_period)
    Dim i, j, k, new_j, temp_j, progression, compteur
    compteur = 0
    new_j = 1
    For i = 2 To sheets(3).size.x
        For j = new_j To sheets(1).size.x
            'Debug.Print "i="; i, d2d1(i, 2, sheets(3)), "j="; j, d2d1(j, 1, sheets(1))
            If (sheets(3).data(d2d1(i, 2, sheets(3))) = sheets(1).data(d2d1(j, 1, sheets(1)))) Then
                temp_j = j
                For k = 1 To period.delta
                        Debug.Print Month(sheets(1).data(d2d1(j, 2, sheets(1)))), Month(period.value(k)), Year(sheets(1).data(d2d1(j, 2, sheets(1)))), Year(period.value(k))
                        If (Month(sheets(1).data(d2d1(j, 3, sheets(1)))) = Month(period.value(k)) And Year(sheets(1).data(d2d1(j, 3, sheets(1)))) = Year(period.value(k))) Then
                            sheets(3).sheetName.Cells(i, k + 2) = sheets(3).sheetName.Cells(i, k + 2) + sheets(1).data(d2d1(j, 3, sheets(1))) * 1.196
                            Debug.Print "sheets="; sheets(3).sheetName.Cells(i, k + 2)
                        End If
                Next k
            End If
        Next j
        new_j = temp_j
        compteur = compteur + 1
        Debug.Print compteur, sheets(3).size.x, Int(100 * (compteur + 1) / sheets(3).size.x)
    Next i
End Sub

Voici le code complet :

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
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
 
Option Explicit
 
Private Type Coordinate
    x As Long
    y As Long
End Type
 
Private Type stSheet
    dataIndex As Coordinate
    size As Coordinate
    data() As Variant
    sheetName As Worksheet
End Type
 
Private Type Type_period
    delta As Long
    value() As Date
End Type
 
Private Enum shConstants
    sheetNumber = 3
End Enum
 
 
 
 
Sub bilan()
 
    Dim sheets() As stSheet, period As Type_period
 
    ReDim sheets(1 To shConstants.sheetNumber) As stSheet
 
    Call initSheetType(sheets())
    Call sheetIntoArray(sheets())
    Call displaySheets(sheets())
    Call getPeriod(sheets(2), period)
    Call initSheet(sheets(3), period)
    Call computeData(sheets(), period)
 
End Sub
 
Private Sub initSheetType(sheets() As stSheet)
 
    Set sheets(1).sheetName = F1
            sheets(1).dataIndex.x = 1
            sheets(1).dataIndex.y = 1
    Set sheets(2).sheetName = F2
            sheets(2).dataIndex.x = 2
            sheets(2).dataIndex.y = 1
    Set sheets(3).sheetName = F3
            sheets(3).dataIndex.x = 2
            sheets(3).dataIndex.y = 3
 
    Dim k, i, j
    For k = 1 To shConstants.sheetNumber
        sheets(k).size.x = 0
        sheets(k).size.y = 0
        i = 0
        j = 0
        Call sizeOfSheet(sheets(k), i, j, 1, 1)
    Next k
End Sub
 
Private Sub initSheet(sheets As stSheet, period As Type_period)
    Call erasePreviousTemplateDataSheet(sheets, period) 'Efface les précédentes valeurs et la mise en forme des cellules
    Call zeroSheet(sheets, period) 'Initialise à 0 toutes les cellules affichant un montant
    Call writePeriod(sheets, period) 'Ecrit la nouvelle période d'analyse dans la sheet
    Call createTemplate(sheets, period) 'Création du template de la sheet
    Call addBorders(sheets, period) 'Separer les totaux des autres résultats par une bordure
    Call nameLineRow(sheets, period) 'Nommage des colonnes/lignes
End Sub
 
Private Sub erasePreviousTemplateDataSheet(sheets As stSheet, period As Type_period)
    Dim i, j
    i = 1
    j = sheets.dataIndex.y
    Do
        Do
            sheets.sheetName.Cells(i, j) = ""
            If (i >= sheets.dataIndex.x) Then
                sheets.sheetName.Cells(i, j).Interior.ColorIndex = xlNone
                sheets.sheetName.Cells(i, j).Font.Bold = False
                If (j = period.delta + sheets.dataIndex.y) Then
                    sheets.sheetName.Cells(i, j).Borders(xlEdgeLeft).LineStyle = xlNone
                End If
            End If
            j = j + 1
        Loop While sheets.sheetName.Cells(i, j) <> ""
 
        j = sheets.dataIndex.y
        i = i + 1
    Loop While sheets.sheetName.Cells(i, j) <> ""
End Sub
 
Private Sub zeroSheet(sheets As stSheet, period As Type_period)
    Dim i, j
    For i = sheets.dataIndex.x To sheets.size.x - 1
        For j = sheets.dataIndex.y To period.delta + 2
            sheets.sheetName.Cells(i, j) = 0
        Next j
    Next i
End Sub
 
Private Sub writePeriod(sheets As stSheet, period As Type_period)
    Dim i
    For i = 0 To period.delta - 1
        sheets.sheetName.Cells(1, i + sheets.dataIndex.y) = period.value(i + 1)
    Next i
End Sub
 
Private Sub createTemplate(sheets As stSheet, period As Type_period)
    Dim i, j
    For i = sheets.dataIndex.x To sheets.size.x
        For j = 1 To period.delta + sheets.dataIndex.y
            If (i Mod 2 = 0) Then
                sheets.sheetName.Cells(i, j).Interior.ColorIndex = 15
            Else
                sheets.sheetName.Cells(i, j).Interior.ColorIndex = 16
                sheets.sheetName.Cells(i, j).Font.ColorIndex = 2
            End If
        Next j
    Next i
End Sub
 
Private Sub addBorders(sheets As stSheet, period As Type_period)
    Dim i
    For i = 2 To sheets.size.x
        Cells(i, period.delta + sheets.dataIndex.y).Borders(xlEdgeLeft).LineStyle = xlContinuous
        Cells(i, period.delta + sheets.dataIndex.y).Borders(xlEdgeLeft).Weight = xlThin
        Cells(i, period.delta + sheets.dataIndex.y).Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    Next i
End Sub
 
Private Sub nameLineRow(sheets As stSheet, period As Type_period)
    sheets.sheetName.Cells(1, 1) = "NOM"
    sheets.sheetName.Cells(1, 2) = "NUM CLIENT"
    sheets.sheetName.Cells(1, period.delta + 3) = "TOTAL CLIENT"
    sheets.sheetName.Cells(sheets.size.x, 1) = "TOTAL MOIS"
End Sub
 
Private Sub sizeOfSheet(sheets As stSheet, i, j, x, y)
    Do
        i = i + 1
    Loop While sheets.sheetName.Cells(i, y) <> ""
 
    Do
        j = j + 1
    Loop While sheets.sheetName.Cells(x, j) <> ""
 
    sheets.size.x = i - 1
    sheets.size.y = j - 1
End Sub
 
Private Sub sheetIntoArray(sheets() As stSheet)
    Dim k, i, j
    For k = 1 To shConstants.sheetNumber
        ReDim sheets(k).data(1 To (sheets(k).size.x * sheets(k).size.y))
        For i = 1 To sheets(k).size.x
            For j = 1 To sheets(k).size.y
                sheets(k).data((j - 1) + (i - 1) * sheets(k).size.y + 1) = sheets(k).sheetName.Cells(i, j)
            Next j
        Next i
    Next k
End Sub
 
Private Sub displaySheets(sheets() As stSheet)
    Dim k, i, j
    For k = 1 To shConstants.sheetNumber
        For i = 1 To sheets(k).size.x
            For j = 1 To sheets(k).size.y
                If (j Mod sheets(k).size.y = 0) Then
                End If
            Next j
        Next i
    Next k
End Sub
 
Private Sub getPeriod(sheets As stSheet, period As Type_period)
    Dim i, j, offset(2), dates(2), add_year, theMonth, theYear
 
    offset(1) = 0
    offset(2) = 0
 
    dates(1) = sheets.sheetName.Cells(2, 1)
    dates(2) = sheets.sheetName.Cells(2, 2)
 
    If (Year(dates(1)) - Year(dates(2)) = 0) Then
        offset(1) = Abs(Month(dates(1)) - Month(dates(2))) + 1
        period.delta = offset(1)
 
    Else
        offset(1) = 12 - Month(dates(1)) + 1
        offset(2) = Month(dates(2))
        period.delta = offset(1) + offset(2) + 12 * (Abs(Year(dates(1)) - Year(dates(2))) - 1)
 
    End If
 
    add_year = 0
    theMonth = Month(dates(1))
    theYear = Year(dates(1))
 
    ReDim period.value(1 To period.delta)
    For i = 0 To period.delta - 1
        If ((theMonth + i) Mod 13 = 0) Then
            add_year = add_year + 1
            theMonth = theMonth + 1
        End If
 
        period.value(i + 1) = "01/" & ((theMonth + i) Mod 13) & "/" & (theYear + add_year)
        Debug.Print period.value(i + 1)
    Next i
End Sub
 
Private Sub computeData(sheets() As stSheet, period As Type_period)
    Dim i, j, k, new_j, temp_j, progression, compteur
    compteur = 0
    new_j = 1
    For i = 2 To sheets(3).size.x
        For j = new_j To sheets(1).size.x
            'Debug.Print "i="; i, d2d1(i, 2, sheets(3)), "j="; j, d2d1(j, 1, sheets(1))
            If (sheets(3).data(d2d1(i, 2, sheets(3))) = sheets(1).data(d2d1(j, 1, sheets(1)))) Then
                temp_j = j
                For k = 1 To period.delta
                        Debug.Print Month(sheets(1).data(d2d1(j, 2, sheets(1)))), Month(period.value(k)), Year(sheets(1).data(d2d1(j, 2, sheets(1)))), Year(period.value(k))
                        If (Month(sheets(1).data(d2d1(j, 3, sheets(1)))) = Month(period.value(k)) And Year(sheets(1).data(d2d1(j, 3, sheets(1)))) = Year(period.value(k))) Then
                            sheets(3).sheetName.Cells(i, k + 2) = sheets(3).sheetName.Cells(i, k + 2) + sheets(1).data(d2d1(j, 3, sheets(1))) * 1.196
                            Debug.Print "sheets="; sheets(3).sheetName.Cells(i, k + 2)
                        End If
                Next k
            End If
        Next j
        new_j = temp_j
        compteur = compteur + 1
        Debug.Print compteur, sheets(3).size.x, Int(100 * (compteur + 1) / sheets(3).size.x)
    Next i
End Sub
 
Private Function d2d1(i, j, sheets As stSheet)
    d2d1 = j + (i - 1) * sheets.size.y
End Function

**tedo01** · 11/10/2012, 20h41

Bonjour,
Dans les deux lignes suivantes est-ce que c'est normal que le deuxième paramètre de la fonction d2d1 soit 2 pour le Debug.Print et 3 pour le If ?

Code :

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

1
2
3
4
5
6
7
 
-----------------------------------------+--------------------------------------------------------------------+
                                         V                                                                    V
Debug.Print Month(sheets(1).data(d2d1(j, 2, sheets(1)))), Month(period.value(k)), Year(sheets(1).data(d2d1(j, 2, sheets(1)))), Year(period.value(k))
If (Month(sheets(1).data(d2d1(j, 3, sheets(1)))) = Month(period.value(k)) And Year(sheets(1).data(d2d1(j, 3, sheets(1)))) = Year(period.value(k))) Then
                                 ^                                                                        ^
---------------------------------+------------------------------------------------------------------------+

**darkwall_37** · 11/10/2012, 20h49

J'ai envie de dire AHAHAHAHAH. Je crois que je suis resté trop longtemps devant l'écran aujourd'hui ;D

Merci c'est parfait, ça fonctionne au taquet. Et au lieu que ça prenne 1 min, çà prends 2s à peine ^^

Je suspecte un probleme de type

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager