Discussion :

[Le ptit nouveau]

Bonjour a tous,

Le temps d'exécution de ma macro dure une éternité... je pense que c'est à cause des formules que j'ai ajouté dans mon tableau (colonnes K/L/M), car avant ces formules, ma maccro était très rapide (environ 1sec).
Je voulais voir avec vous s'il y avait des possibilités pour réduire le temps d'exécution, mon excel est inutilisable tellement c'est long et je ne sais absolument pas comment faire...
Je vous joins directement le fichier excel, je pense que ca sera plus parlant !
Fichier test.xlsm


Merci de votre aide

[sorbetframboise]

Sur ce site nous n'ouvrons pas les fichiers tu devrais plutôt mettre ton code avec la balise associée

[Le ptit nouveau]

C'est que vous ne pouvez pas ouvrir le fichier ? Ou alors c'est plus pour des mesures de sécurités ?

En attendant, voici 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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Sub Ajout_Data()
 
Dim i As Integer
Dim j As Integer
Dim NBRepere As Integer
Dim NBVissage As Integer
 
 
Application.ScreenUpdating = False
 
Sheets("Data").Activate
Range("J2:J1048576").ClearContents
 
NBVissage = Application.WorksheetFunction.CountA(Sheets("Repere").Range("A:A")) - 1
NBRepere = Application.WorksheetFunction.CountA(Sheets("Data").Range("A:A"))
i = 2
j = 2
 
 
Do While (i <= NBRepere)
 
 
    If Sheets("Data").Range("I" & i).Value Like "" Then
        j = 1
    ElseIf Sheets("Data").Range("I" & i).Value Like "B" & "*" Then
        Sheets("Data").Range("J" & i) = Sheets("Repere").Range("A" & j)
    Else: Sheets("Data").Range("J" & i) = Sheets("Repere").Range("A" & j)
        j = j - 1
    End If
 
    j = j + 1
    i = i + 1
 
    If j <= NBVissage + 1 Then
 
    Else: j = j - NBVissage + 1
 
    End If
Loop
 
 
Sheets("Analyse").PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
Application.ScreenUpdating = True
MsgBox ("Les répères ont bien été ajouté")
 
End Sub

Pour info, ce code me permet de remplir entre 5 000 et 15 000 lignes en fonction du nombre de données que je vais avoir à traiter.

[Philippe Tulliez]

Bonjour,
A la lecture de votre code, il semble que vous remplissez les cellules de la colonne J de la feuille [Data] suivant critères.
Ne serait-ce pas plus simple d'écrire la formule dans cette colonne ?
S'il faut vraiment passer par du VBA, j'écrirais la formule directement sur l'ensemble de la plage concernée ce qui vous éviterait d'écrire à chaque ligne une valeur et si la formule doit absolument être bannie, on l'insère dans la colonne et ensuite on fait l'équivalent d'un Copier / collage spécial valeur

Voir mes billets titrés

• Comment écrire une formule dans une plage de cellules en VBA ?
• Comment écrire une formule plus complexe dans une cellule ?

[Le ptit nouveau]

Peut-être qu'écrire la formule directement dans la cellule est la solution, cependant je pense que cette formule est bien trop compliquée, et je ne vois pas comment ne pas passer par du code...
Comme vous n'ouvrez pas les fichiers Excel sur ce site, est-ce qu'il y a un moyen de vous partager quelques données pour mieux comprendre le problème ?
Sinon je peux essayer avec des mots, mais ca risque d'être plus compliqué a comprendre.

[Philippe Tulliez]

Bonjour,
J'ai peut-être mal lu mais il me semble que la formule se résume à deux SI imbriqués

[Le ptit nouveau]

Deux Si imbriqués mais toutes les 33 lignes (ou plus en fonction du nombre de reprises), il faut reprendre à zéro.
En gros, les 33 lignes représentent un produit fini. Il faut 33 vissages pour faire un produit fini, d'où les 33 lignes de données.
Cependant, il se peut que j'ai des reprises lors de mes vissages, donc s'il y a 2 reprises, j'aurais 35 lignes de données. Et je veux qu'à la fin de ces 33 + x reprises, je reprenne ma numérotation à 0 car je passe sur une nouvelle pièce.
La mon code me permet de donner un nom à chacune de les lignes de données Excel comme par exemple C1 pour le vissage 1, C2 pour le 2, V1 pour le 3, ect.

Est-ce possible de faire ça uniquement avec une formule dans une cellule ?

[Philippe Tulliez]

Bonjour,
Effectivement, c'est plus complexe qu'un simple SI imbriqué.
Si vous avez une version Excel 2010 ou supérieure, je pense que vous pourriez résoudre ce problème plus simplement et surtout plus rapidement avec Power Query mais il faudrait donner une explication claire de ce que vous attendez avec une image des données concernées

[Le ptit nouveau]

Alors voici exactement comment ça se présente :
Je récupère déjà mes données avec Power Query, et voici les données que j'arrive à extraire :



Ensuite, avec ma macro actuelle, je viens ajouter le nom des répères; voici ce que j'obtiens :



L'ordre des vissages est toujours le même, on commence par C1 et on fini par V38. Ce qui va changer, c'est quand il y a des reprises ou non. Par exemple, il peut y avoir 5 reprises sur le même repère, nous aurons donc 6 lignes (les 5 reprises + le vissage bon).

Ensuite, les données que j'ai à ce moment ne me suffisent pas pour faire toutes mes analyses, j'ai donc ajouté 3 colonnes dans mon tableau :

- La première pour savoir s'il s'agissait soit d'un vissage bon ("B"), soit d'un vissage mauvais ("M"). Cette colonne me sert juste à faciliter l'écriture de la formule de la colonne suivante.
- La deuxième colonne qui me donne le nombre de vissage qu'il y a eu par pièce et par repère. Si on reprend les photos ci-dessus, j'aurais comme valeur 1 pour la ligne 18, et 2 pour la ligne 19. A chaque fois je dis que si le vissage est bon, alors c'est égale à 1, mais si c'est une reprise, c'est égale à la valeur de la cellule précédente + 1.
- La troisième qui me donne le statut du vissage. Par exemple, pour certains repères je n'ai pas le droit de faire de reprise, ou alors, sur d'autres repères j'ai le droit de ne faire que 2 reprises maximum. Au final, j'ai 4 possibilités : le vissage est OK, il y a eu reprise mais c'est autorisé, il y a eu trop de reprises, et la reprise est interdite.
Au final, voici ce que j'ai :



Normalement tout fonctionne parfaitement, il y a juste cette histoire de mettre les repères avec la maccro qui prend un temps monstrueux (je parle de plusieurs minutes), et j'ai l'impression que c'est depuis que j'ai ajouté les 3 dernières colonnes... Sauf que j'en ai besoin pour mes analyses.

J'espère avoir été assez clair.

[Pierre Dumas]

Bonjour

Si j'ai bien compris, la macro publiée au post 3 est celle qui fabrique la colonne J. Est-ce que cela est bien le cas ?
Est-ce qu'il est possible d'avoir une copie écran de la colonne A de la feuille "Repere" ?

Est-ce qu'il est possible d'avoir les formules en K, L et M ?

Bonne fin d'après-midi

Pierre Dumas

[Le ptit nouveau]

Bonjour Pierre,

Oui, il s'agit bien de la macro qui alimente la colonne J.
Voici la colonne A de la feuille Repere :



Et voici les 3 formules :
- Colonne K :

Code :

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

=SI.MULTIPLE(GAUCHE([@CR];1);"B";"B";"M";"M";"";"")

- Colonne L :

Code :

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

=SI([@[BON/MAUVAIS]]="M";1+L1;1)

- Colonne M :

Code :

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

=SI.CONDITIONS(OU(ET(J3="C27";L3>2);ET(J3="C28";L3>2);ET(J3="V23";L3>2);ET(J3="V24";L3>2));Repere!$B$2;OU(L3=2;L3=3);Repere!$B$3;J3="";"";L3>3;Repere!$B$4;L3=1;Repere!$B$5)

Bonne fin de journée !

[EricDgn]

Bonjour,

En effectuant les calculs dans des tableaux (array), c'est presque instantané.

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
Option Explicit
 
Sub Ajout_Data()
    Dim loData As ListObject, loRep As ListObject
    Dim i As Integer, j As Integer
    Dim NbRepere As Integer, NbVissage As Integer
    Dim Repere As Variant, CR As Variant, Rep As Variant
    Dim Rng As Range, sT As String
    Dim t As Single
    t = Timer
    Sheets("Data").Activate
    Set loData = Worksheets("Data").ListObjects("Donnees")
    Set loRep = Worksheets("Repere").ListObjects("t_repere")
    Set Rng = loData.ListColumns("Rep").DataBodyRange   '--- Rep = titre colonne J
    Rng.ClearContents
    Repere = Application.Transpose(loRep.ListColumns("Repere").DataBodyRange)
    CR = Application.Transpose(loData.ListColumns("CR").DataBodyRange)
    NbVissage = loRep.DataBodyRange.Rows.Count
    NbRepere = loData.DataBodyRange.Rows.Count
    Debug.Print NbVissage, NbRepere
    ReDim Rep(NbRepere)
    i = 1
    j = 1
    Do
        If CR(i) Like "B*" Then
            Rep(i) = Repere(j)
        ElseIf CR(i) = "" Then
            j = 0
        Else
            Rep(i) = Repere(j)
            j = j - 1
        End If
        j = j + 1
        i = i + 1
    Loop Until i > NbRepere
    Set Rng = loData.ListColumns("Rep").Range
    sT = Rng(1)                                 '--- récupère le titre
    Rng.Value = Application.Transpose(Rep)      '--- colle les valeurs calculées
    Rng(1) = sT                                 '--- remt le titre (écrasé à l'instruction précédente)
    'Sheets("Analyse").PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
    Debug.Print Timer - t
    MsgBox ("Les répères ont bien été ajouté en " & (Timer - t) & " sec.")
End Sub

Il y a juste les lignes 34 à 36 de votre code qui n'est pas repris, n'en ayant pas compris l'objet.

Cordialement.

[Le ptit nouveau]

Bonjour Eric,

Merci beaucoup pour ta solution. Le temps d'exécution est quasi instantané. C'est top !

J'aurais juste une dernière question, je n'arrive pas trop à comprendre à quoi servent les lignes 16/17/20, soit celles-ci :

Code :

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

1
2
3
4
5
Repere = Application.Transpose(loRep.ListColumns("Repere").DataBodyRange)
CR = Application.Transpose(loData.ListColumns("CR").DataBodyRange)
.
.
Debug.Print NbVissage, NbRepere

Je débute en VBA, est-ce que tu pourrais m'expliquer un peu plus précisément ce qu'est cette fonction Transpose, et à quoi sert ce Debug.Print stp ?

Mais encore une fois merci beaucoup pour ton aide !

[EricDgn]

Bonjour,

Pour les explications, le mieux est de faire des recherches sur internet.

Transpose sert essentiellement à passer d'une orientation verticale à une orientation horizontale, ce qui est nécessaire lorsque que l'on remplit une table (array), qui est en quelque sorte par défaut "horizontale", à l'aide de ce qui se trouve dans une plage (range) qui est verticale.

Le Debug.Print ne sert qu'à afficher des informations dans la fenêtre Exécution de l'éditeur VBA (Ctrl-g)

Un petit exemple en attaché.

Cordialement.

[Le ptit nouveau]

Bonjour,

Très bien, je comprend mieux.

Merci pour tout et bonne journée !