Discussion :

[CalatheaWF]

Bonjour,



J'ai une macro qui converti des données (date en vrai format date/ ajout d'une colonne pour le numéro de semaine) et pour ça j'ai 3 boucles FOR. La macro met 40 minutes à tournée pour seulement 1200 lignes, je cherche donc a optimiser ces boucles.
=> Solution 1 : copier coller les données brutes dans un nouvel Excel et faire les conversion dans celui-ci, pour re-copier/coller la conversion dans l'onglet original mais je bloque sur la dernière partie parce que mon fichier tampon n'est pas nommé et je ne souhaite pas le sauvegarder.
=> Solution 2 : Optimiser les FOR ?

Si quelqu'un à une autre proposition..

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
 
 
Option Explicit
Sub ConversionIntermédiaire()
 
 
Dim XLBook As Workbook
Dim XLSheet As Worksheet
Dim RforRange As Range
Dim t As Variant
Dim i As Variant
Dim l As Variant
Dim FichierOriginal As Worksheet
 
 
'Ouverture nouvel XL (sans sauvegarde)
Set XLSheet = ActiveWorkbook.Worksheets("Données_p_conversion")
Set XLBook = Application.Workbooks.Add(xlWBATWorksheet)
'Coller l'onglet des données_p_conversion
Set RforRange = XLBook.Worksheets(1).Range("A1")
  XLSheet.UsedRange.Copy RforRange
  t = XLSheet.UsedRange.Value
  RforRange.Resize(UBound(t, 1), UBound(t, 2)).Value = t
 
 
 
'Sélection de la feuille à convertir (du nouvel XL)
Sheets("Feuil1").Select
With Worksheets("Feuil1")
 
'Boucle pour convertir en vrai format date
i = 2
While Cells(i, 1) <> "": i = i + 1: Wend
 
For l = 2 To i - 1
Cells(l, 3) = convertdat(Cells(l, 3))
Cells(l, 6) = convertdat(Cells(l, 6))
Cells(l, 7) = convertdat(Cells(l, 7))
Next
 
'Ajout d'une colonne
Columns("F:F").Insert Shift:=xlToRight
'Renommer son en-tête de colonne
Cells(1, 6) = "Semaine"
 
'Boucle pour écrire le numéro de semaine dans la nouvelle colonne
For l = 2 To i - 1
Cells(l, 6) = DatePart("ww", Cells(l, 7), vbMonday, vbFirstFourDays)
Cells(l, 6).NumberFormat = "general"
Next
For l = 2 To i - 1
Cells(l, 4).NumberFormat = "@"
Next
 
'Suppression des colonnes inutiles
Range(Worksheets("Feuil1").Range("M1"), Worksheets("Feuil1").Range("M1").End(xlDown)).Clear
Range(Worksheets("Feuil1").Range("N1"), Worksheets("Feuil1").Range("N1").End(xlDown)).Clear
Range(Worksheets("Feuil1").Range("O1"), Worksheets("Feuil1").Range("O1").End(xlDown)).Clear
Range(Worksheets("Feuil1").Range("P1"), Worksheets("Feuil1").Range("P1").End(xlDown)).Clear
 
 
End With
 
 
 
'Retourner sur l'onglet données_p_conversion original
Set FichierOriginal = Workbooks("Essai COUV SK").Worksheets("Données_p_conversion")
'Suppression du contenu
Sheets("Données_p_conversion").Cells.ClearContents
'A FAIRE // Copier/coller
 
 
End Sub
 
----------
 
Function convertdat(dat)
    convertdat = DateSerial(Left(dat, 4), Mid(dat, 5, 2), Right(dat, 2))
End Function

Essai COUV SK.xlsm
Merci par avance,

[mat955]

Bonjour,

Déjà, à partir du moment où tu as 3 fois la même boucle, pourquoi ne pas regrouper toutes les instructions dans une seule et même boucle ?
D'abord tu ajoutes la colonne dont tu as besoin, et ensuite tu fais une seule boucle avec tes instructions.

Ensuite, concernant les boucles très gourmandes en temps d'exécution, il existe une solution qui consiste à mettre tes données dans une grande variable tableau, et de faire les opérations dans cette variable.
Puis à la fin, tu renvoies tes données modifiées dans tes cellules Excel.
Les opérations sur un Array sont beaucoup moins gourmandes que de le faire sur Excel, mais genre beaucoup moins.

[CalatheaWF]

Merci pour l'aiguillage !

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
 
'Ajout d'une colonne
Columns("F:F").Insert Shift:=xlToRight
'Renommer son en-tête de colonne
Cells(1, 6) = "Semaine"
 
 
'Boucle pour convertir en vrai format date
i = 2
While Cells(i, 1) <> "": i = i + 1: Wend
 
For l = 2 To i - 1
Cells(l, 3) = convertdat(Cells(l, 3))
Cells(l, 7) = convertdat(Cells(l, 7))
Cells(l, 8) = convertdat(Cells(l, 8))
 
 
'Boucle pour écrire le numéro de semaine dans la nouvelle colonne
Cells(l, 6) = DatePart("ww", Cells(l, 7), vbMonday, vbFirstFourDays)
Cells(l, 6).NumberFormat = "general"
Cells(l, 4).NumberFormat = "@"
Next

par contre mon set à la fin ne fonctionne plus je ne comprends pas :

Code :

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

1
2
3
4
5
 
'Retourner sur l'onglet données_p_conversion original
Set FichierOriginal = Workbooks("Essai COUV STOCK").Worksheets("Données_p_conversion")
'Suppression du contenu
Sheets("Données_p_conversion").Cells.ClearContents

erreur d'exécution 9, l'indice n'appartient pas à la selection.


Il me semble voir déjà vu des codes avec du Array, je vais creuser un peu.

[Alex020181]

Bonjour,

En me basant sur ton fichier je te propose ce code qui te reporte à droite les dates converties et les n° de semaines ISiO 4

A toi de finaliser mais le principal est là. Il te suffit de cliquer sur le bouton puis de comprendre le code pour monter en compétence.

Teste et dis nous.

[Qwazerty]

Salut

Et pourquoi pas sans VBA... du moins si ta version d'excel te le permet(je n'avais pas vu le TAG XL-365), avec l'utilisation de PowerQuery.
Les transformations que j'ai faites sont sans doute perfectible, je débute avec l'utilisation de PQ... D'ailleurs si un utilisateur chevronné passe par là...

Dans les faits, tu as juste à convertir tes données en tableau structuré pour les manipuler dans PQ (Il suffit d'utiliser le même nom de tableau structuré mais il est aussi possible de modifier le code PQ pour le rendre paramétrable)

++
Qwaz

[mat955]

Re,

Autre chose améliorable dans ton code, que j'ai survolé la première fois :

Code :

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

1
2
i = 2
While Cells(i, 1) <> "": i = i + 1: Wend

Je comprends pas là que tu veux atteindre la dernière ligne non vide.
Pour cela, utilises plutôt :

Code :

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

i = Cells(2,1).end(xlDown).row

C'est l'équivalent du CTRL + BAS sur le clavier.