Problème avec "While"

Version imprimable

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:

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 :-)

01/02/2017, 16h02
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

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:

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 :-)

02/02/2017, 11h26
unparia

Bonjour

Citation:

Envoyé par halaster08

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

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:

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

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:

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 :-)

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:

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

Merci, j'ai à peu près tout compris, je suis en train de finaliser ça.

J'en suis arrivé là car à la base, j'avais mis la formule ci-dessous dans 30 000 cases
Code:

1 2 =SOMMEPROD(('J1'!$A$2:$A$35000=Articles!B2)*('J1'!$C$2:$C$35000=Articles!$C$1)*'J1'!$D$2:$D$35000)+SOMMEPROD(('J2'!$A$2:$A$35000=Articles!B2)*('J2'!$C$2:$C$35000=Articles!$C$1)*'J2'!$D$2:$D$35000)+SOMMEPROD(('J3'!$A$2:$A$35000=Articles!B2)*('J3'!$C$2:$C$35000=Articles!$C$1)*'J3'!$D$2:$D$35000)+SOMMEPROD(('J4'!$A$2:$A$35000=Articles!B2)*('J4'!$C$2:$C$35000=Articles!$C$1)*'J4'!$D$2:$D$35000)+SOMMEPROD(('J5'!$A$2:$A$35000=Articles!B2)*('J5'!$C$2:$C$35000=Articles!$C$1)*'J5'!$D$2:$D$35000)+SOMMEPROD(('J6'!$A$2:$A$35000=Articles!B2)*('J6'!$C$2:$C$35000=Articles!$C$1)*'J6'!$D$2:$D$35000)+SOMMEPROD(('J7'!$A$2:$A$35000=Articles!B2)*('J7'!$C$2:$C$35000=Articles!$C$1)*'J7'!$D$2:$D$35000)
En gros, c'est toujours la même formule mais qui va pointer dans des fiches différentes "J1" à "J7".
Comme tu peux t'en douter, Excel n'a pas vraiment aimé ma façon de travailler xD.

Donc j'ai voulu trouver une autre solution et me voilà ici :-)
Du coup, je vais tester ta super méthode qui permet de faire les calculs sans garder les formules dans les cases.

Je reviens vers toi si je n'y arrive pas. Merci :-)

Edit : Petite question, dans ta formule ci-dessous :
Code:

1 2 SUMPRODUCT((J1!" & PlageSource.Address(True, True) & "=B2)*(J1!" & PlageSource.Offset(0, 3).Address(True, True) & "))"
Pourquoi tu as mis "PlageSource.Offset(0, 3)" ? Je ne comprends pas par rapport à ta formule "SOMMEPROD(('J1'!$A$2:$A$11=B2)*('J1'!$D$2:$D$11))"

Merci, ça fonctionne super bien :-D
J'ai testé avec le code ci-dessous :
Code:

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 Sub Super_toto() Dim PS_J1 As Range ', PS_J2 As Range, PS_J3 As Range, PS_J4 As Range, PS_J5 As Range, PS_J6 As Range, PS_J7 As Range Dim PlageDest As Range With ThisWorkbook Set PS_J1 = .Worksheets("J1").Cells(2, 1).Resize(.Worksheets("J1").Cells(.Worksheets("J1").Rows.Count, 1).End(xlUp).Row - 1, 1) Set PlageDest = .Worksheets("Articles").Cells(2, 3).Resize(.Worksheets("Articles").Cells(.Worksheets("Articles").Rows.Count, 2).End(xlUp).Row - 1, 1) End With With PlageDest '=SOMMEPROD(('J1'!$A$2:$A$35000=Articles!B2)*('J1'!$C$2:$C$35000=Articles!$C$1)*'J1'!$D$2:$D$35000) .Formula = "=SUMPRODUCT((J1!" & PS_J1.Address(True, True) & "=B2)*('J1'!" & PS_J1.Offset(0, 2).Address(True, True) & "=$C$1)*(J1!" & PS_J1.Offset(0, 3).Address(True, True) & "))" .Value = .Value End With With PlageDest.Offset(0, 1) '=SOMMEPROD(('J1'!$A$2:$A$35000=Articles!B2)*'J1'!$D$2:$D$35000) .Formula = "=SUMPRODUCT((J1!" & PS_J1.Address(True, True) & "=B2)*(J1!" & PS_J1.Offset(0, 3).Address(True, True) & "))" .Value = .Value End With MsgBox "Calculs Terminés" End Sub
Bon très honnêtement, je ne dois pas avoir ton cheval de course car pour moi ça prend 5min ^^
D'ailleurs, je ne comprend pas pourquoi mes 4 processeurs chargent 2 fois de 0 à 100% :-/
De plus, j'ai resté avec deux boucles "With" séparées l'une de l'autres comme ci-dessus et deux boucles imbriquées l'une dans l'autre (comme dans ton exemple) et je ne remarque pas de différence au niveau du temps de calcul. Penses-tu que c'est normal ? Je pensais qu'avec deux boucles "With" séparées, les calculs iraient plus vite mais se n'est pas le cas xD ^^

Edit : Est-ce que tu aurais une astuce pour accélérer les calculs ?

a) En effet, pour le moment, je fais mon calcul avec la feuille "J1", mais je dois faire la même chose de J1 à J7. Cela risque donc de prendre 7 fois plus de temps donc au moins 5min x 7

b) De plus, mes formules =SUMPROD(J1 BLABLA), vont devenir =SUMPROD(J1 BLABLA) + SUMPROD(J2 BLABLA) + ... + SUMPROD(J7 BLABLA)

c) Est-ce qu'il est préférable de faire des opérations dans des feuilles séparées ou grouper toutes les données sur une seul feuille ?

Merci par avance :-)