Discussion :

[rocktrak]

Bonjour à tous,

Je suis novice en VBA et je ne comprends pas pourquoi mon petit code bloque au niveau du "while". Je suis sur Office 2010.

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
 
Dim x&
Dim y&
Dim n_&
Dim T&
Dim num_n_ As String
Dim num_CP&
 
Sub test()
'
' test Macro
'
''''Nombre de tickets
T = 0
''''Nombre de lecture
n_ = 0
''''Numéro de ligne data
x = 2
''''Numéro de ligne du type de compteur
y = 2
'''' Référence de la lecture
num_n_ = Sheets(9).Cells(3, 1).Value
 
num_CP = Sheets(9).Cells(2, y).Value
 
b = Sheets(1).Cells(1, x).Value
 
While x < 40000
 
    If num_CP = Sheets(1).Cells(1, x).Value Then
        T = T + Sheets(1).Cells(4, x).Value
        If Sheets(1).Cells(3, x).Value = num_n_ Then
            n_ = n_ + Sheets(1).Cells(4, x).Value
        Else:
        End If
 
    Else:
   End If
 
x = x + 1
 
Wend
 
Sheets(10).Cells(1, 1).Value = T
Sheets(10).Cells(2, 1).Value = n_
Sheets(10).Cells(3, 1).Value = x
Sheets(10).Cells(4, 1).Value = num_n
Sheets(10).Cells(5, 1).Value = num_CP
 
End Sub

Je ne comprends pas pourquoi le programme ne se lance pas.

Merci par avance :-)

[halaster08]

Bonjour,
Pour commencer balise ton code (bouton #) c'est beaucoup plus lisible.
Tes dim ne sont pas dans ta sub, donc sont ignoré, donc VBA choisit le type de x, lui met integer et plante quands tu dépasse la valeur max d'un integer

[rocktrak]

Merci :-)

Effectivement, j'avais un prb dans ma déclaration de variable.

À présent, j'ai un autre problème. En effet, j'ai un double while de 30 000 étapes. Ainsi, la boucle intérieur doit tourner 30 000 * 30 000 fois (mes variables x et y vont aller jusqu'à 30000).
Excel se met donc à ramer avec "Pas de réponse". Est-ce qu'il y a une astuce pour faire du traitement de données en masse ? Est-ce que je dois passer par une autre méthode ?
J'ai entendu parlé de Microsoft Access mais je ne connais pas son fonctionnement.
Ou alors, le bon vieux langage C ^^

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
 
Sub test()
 
Dim z As Long
Dim x As Long
Dim y As Long
Dim n As Long
Dim T As Long
Dim num_n As Long
Dim num_CP As Long
 
''''Nombre de tickets
T = 0
''''Nombre de lecture
n = 0
''''Numéro de ligne data
x = 2
''''Numéro de ligne du type de compteur
y = 2
'''' Lecture normal
num_n = 33
 
'Boucle de traitement de données pour toutes les références CP
Do While Not (IsEmpty(Sheets("Articles").Cells(y, 2)))
'Récupération de la première référence CP
num_CP = Sheets("Articles").Cells(y, 2).Value
 
'Boucle de traitement des données pour une référence de CP
 
Do While Not (IsEmpty(Sheets("J1").Cells(x, 1)))
If num_CP = Sheets("J1").Cells(x, 1).Value Then
        T = T + Sheets("J1").Cells(x, 4).Value
        If Sheets("J1").Cells(x, 3).Value = num_n Then
            n = n + Sheets("J1").Cells(x, 4).Value
        Else:
        End If
 
    Else:
   End If
'Passage à la ligne suivante
x = x + 1
Loop
 
Sheets("Articles").Cells(y, 4).Value = T
Sheets("Articles").Cells(y, 3).Value = n
T = 0
n = 0
x = 2
 
'Passage à la référence CP suivante
y = y + 1
Loop
 
End Sub

Merci par avance :-)

[unparia]

Bonjour

Pour commencer balise ton code (bouton #) c'est beaucoup plus lisible.

[joe.levrai]

Bonjour,

deux formules Excel pour remplir la colonne 3 et 4 de Articles

en 5 lignes de codes c'est plié, pour une vitesse d'exécution qui ne dépassera pas la seconde je présume


EDIT : un exemple partiellement décomposé

Avec formule SOMMEPROD()

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
Sub toto()
    With ThisWorkbook
        Set PlageSource = .Worksheets("J1").Cells(2, 1).Resize(.Worksheets("J1").Cells(.Worksheets("J1").Rows.Count, 1).End(xlUp).Row - 1, 1)
 
        With .Worksheets("Articles")
            With .Cells(2, 6).Resize(.Cells(.Rows.Count, 2).End(xlUp).Row - 1, 1)
                .Formula = "=SUMPRODUCT((J1!" & PlageSource.Address(True, True) & "=B2)*(J1!" & PlageSource.Offset(0, 3).Address(True, True) & "))"
                .Value = .Value
                With .Offset(0, 1)
                    .Formula = "=SUMPRODUCT((J1!" & PlageSource.Address(True, True) & "=B2)*('J1'!" & PlageSource.Offset(0, 2).Address(True, True) & "=33)*(J1!" & PlageSource.Offset(0, 3).Address(True, True) & "))"
                    .Value = .Value
                End With
            End With
        End With
    End With
End Sub

[rocktrak]

Merci Joé :-)

Je vous prie de bien vouloir m'excuser, je ne savais pas comment faire pour rentrer mon code dans une balise.
Mais finalement, c'est tout simple :-)

Je code comme j'ai appris à l'école, c'est à dire avec des if, while, etc... qui ne sont pas forcement adaptés au VBA.

Ça te parait tout simple Joé, mais ton code est pour moi compliqué ^^ Je suis un peu "noob" en VBA et j'apprends.
J'ai compris que ta façon de faire était bien plus efficace, c'est pour cela que je suis en train d'étudier ton code xD

En tout cas merci :-)

Je ne ferme pas tout de suite la discussion car je vais surement avoir d'autres questions xD

Edit : Je viens de voir que pour gagner un peu de temps, je peux rajouter ces instructions dans le code :

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
 
Dim boecran As Boolean, bobarre As Boolean, boevent As Boolean, bosaut As Boolean
Dim icalcul As Integer
 
boecran = Application.ScreenUpdating
bobarre = Application.DisplayStatusBar
icalcul = Application.Calculation
boevent = Application.EnableEvents
bosaut = ActiveSheet.DisplayPageBreaks
 
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
 
''''''''' Le Code
 
Application.ScreenUpdating = boecran
Application.DisplayStatusBar = bobarre
Application.Calculation = icalcul
Application.EnableEvents = boevent
ActiveSheet.DisplayPageBreaks = bosaut
 
End Sub

Alors au niveau de ton code, j'ai compris que "Cells(2, 6).Resize(.Cells(.Rows.Count, 2).End(xlUp).Row - 1, 1)" permettait de descendre d'un rang (si c'est bien ça).

Pour le reste, j'ai vu que tu as utilisé un SOMMEPROD, mais est-ce que cela ne va pas justement écrire cette formule dans des milliers de cases ?
Est-ce que ton SOMMEPROD reste dans ton code et permet de faire les calculs dans le code ou est-ce qu'il sera écrit partout ?

Merci par avance :-)

[joe.levrai]

On est d'autant plus efficace en Excel VBA qu'on cherchera à utiliser au maximum les outils natifs et compilés dans l'application


Un exemple simple :

on peut facilement créer une procédure ou une fonction qui va additionner des cellules
mais, Excel dispose déjà de la fonction SOMME()
Autant utiliser la fonction plutôt que de réinventer la roue en voulant faire du 100% VBA


voici le code annoté avec l'explication des formules
j'ai également un peu plus détaillé en ajoutant une variable pour la plage de destination

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
Sub toto()
Dim PlageSource As Range
Dim PlageDest As Range
    With ThisWorkbook
         ' la plage en J1 qui part de A2 jusqu'à la première ligne non vide en partant de tout en bas
         Set PlageSource = .Worksheets("J1").Cells(2, 1).Resize(.Worksheets("J1").Cells(.Worksheets("J1").Rows.Count, 1).End(xlUp).Row - 1, 1)
         ' la plage en Articles qui part de F2 jusqu'à la première ligne non vide en partant de tout en bas
         Set PlageDest = .Worksheets("Articles").Cells(2, 6).Resize(.Worksheets("Articles").Cells(.Worksheets("Articles").Rows.Count, 2).End(xlUp).Row - 1, 1)
    End With
 
    With PlageDest
        ' on écrit la formule =SOMMEPROD(('J1'!$A$2:$A$11=B2)*('J1'!$D$2:$D$11))
        ' B2 sera remplacé par B3 en ligne 3, B4 en ligne 4 etc...
        .Formula = "=SUMPRODUCT((J1!" & PlageSource.Address(True, True) & "=B2)*(J1!" & PlageSource.Offset(0, 3).Address(True, True) & "))"
        ' on remplace les formules par leur valeur
        .Value = .Value
 
        ' sur la colonne à côté de F2 (=colonne G)
        With .Offset(0, 1)
            ' on écrit la formule =SOMMEPROD(('J1'!$A$2:$A$11=B2)*('J1'!$C$2:$C$11=33)*('J1'!$D$2:$D$11))
            .Formula = "=SUMPRODUCT((J1!" & PlageSource.Address(True, True) & "=B2)*('J1'!" & PlageSource.Offset(0, 2).Address(True, True) & "=33)*(J1!" & PlageSource.Offset(0, 3).Address(True, True) & "))"
            ' on remplace les formules par leur valeur
            .Value = .Value
        End With
    End With
End Sub

Resize permet de "retailler" une plage. Ici je l'utilise pour partir de la première cellule de la colonne, et retailler la taille de cette plage pour y ajouter toutes les cellules non vides en dessous


Déjà avec ça, ça devrait être assez rapide
j'ai testé avec 700 000 lignes dans chacune des deux feuilles et ça prend 10 secondes