Discussion :

[Polygos]

Il n'y a pas de magie en informatique mais là, je suis sec; encore un truc évident sans doute.
Dans un fichier de 150 colonnes et 2000 lignes, j'ai une colonne de stock (la 122) qui contient une formule recherchev qui va chercher une valeur ailleurs, dans un autre classeur.

Le contenu est donc une formule et je veux faire une sélection des lignes (les articles) à prendre en compte sur la base de cette valeur, j'en ai donc créée une autre avec une petite boucle qui contiendra la valeur, nombre entier.
Pour créer la colonne 121, donc:

-je regarde la valeur de la colonne 122
-si différent de 0, je la copie dans une autre colonne (la 121) au format nombre entier.
Code:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
ws1.Activate
 
If MsgBox("voulez-vous mettre le stock à jour ?", vbYesNo, "Confirmation") = vbYes Then
    For i = PL To DL
        If Cells(i, 122) <> 0 Then
        Cells(i, 121) = Cells(i, 122)
        Else
        End If
    Next i
Else
End If

Termps de traitement de cette boucle: 8 mn !

wb1, ws1 noms du fichier source créés préalablement,
PL, DL, première et dernière lignes PL=8, DL= range(("F"&Rows.count).End(xlup).Row, F étant une colonne bien représentative du nombre d'articles.

il y a des tas de raisons d'optimiser un code mais à ce point là, il va chercher les infos en Russie, c'est pas possible !

[halaster08]

Bonjour,

As-tu essayé en désactivant le calcul automatique?

[joe.levrai]

Bonjour,

penser excel ! et optimiser les interactions

1) neutraliser les évènements et rafraichissement d'écran
2) sur l'ensemble de la colonne 121, injecter la formule en colonne 122 "=SI(MaCelluleColonne121<>0;MaCelluleColonne121;"")" et remplacer la formule par les valeurs

pour illustrer (et à adapter), je l'ai fais en R1C1 mais on peut le faire en formule référentielle

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Application.ScreenUpdating = false
Application.EnableEvents = false
Application.Calculation = xlCalculationManual
 
With Range(Cells(PL,121),Cells(DL,121))
    .FormulaR1C1 = "=IF(RC[1]<>0,RC[1],"""")"
    .Value = .Value
End With
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = true
Application.EnableEvents = true

[Polygos]

Bonjour,

penser excel ! et optimiser les interactions

1) neutraliser les évènements et rafraichissement d'écran
2) sur l'ensemble de la colonne 121, injecter la formule en colonne 122 "=SI(MaCelluleColonne121<>0;MaCelluleColonne121;"")" et remplacer la formule par les valeurs

pour illustrer (et à adapter), je l'ai fais en R1C1 mais on peut le faire en formule référentielle

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Application.ScreenUpdating = false
Application.EnableEvents = false
Application.Calculation = xlCalculationManual
 
With Range(Cells(PL,121),Cells(DL,121))
    .FormulaR1C1 = "=IF(RC[1]<>0,RC[1],"""")"
    .Value = .Value
End With
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = true
Application.EnableEvents = true

Bonjour Joe
Mais pourquoi ma bête boucle ne fonctionne pas, alors ? je remplace bien une valeur de cellule par une autre ? cest le fait de passer en calcul manuel ?
Sinon, 121 c'est la cellule de destination, 122 la source; on est bien d'accord ?

[Invité]

Bonjour Joe
Mais pourquoi ma bête boucle ne fonctionne pas, alors ? je remplace bien une valeur de cellule par une autre ? cest le fait de passer en calcul manuel ?
Sinon, 121 c'est la cellule de destination, 122 la source; on est bien d'accord ?

L'utilisation des boucles est efficace mais pose pas mal de problèmes dès qu'on utilise un grand nombre de données. Traiter 2000 lignes est déjà très grand pour une boucle, surtout si tu ne désactives pas le ScreenUpdating (qu'il faut bien réactiver à la fin du code bien sur).

[halaster08]

Mais pourquoi ma bête boucle ne fonctionne pas, alors ? je remplace bien une valeur de cellule par une autre ? cest le fait de passer en calcul manuel ?

En calcul automatique, a CHAQUE itération de ta boucle excel recalcule TOUTES les formules du classeur, et comme tu as un certains nombres de rechercheV ça peut expliquer la lenteur.

[Polygos]

En calcul automatique, a CHAQUE itération de ta boucle excel recalcule TOUTES les formules du classeur, et comme tu as un certains nombres de rechercheV ça peut expliquer la lenteur.

Ah ! ok ! et Dieu sait s'il y en a !

Je connais mal la notation relative: RC[1], c'est bien R[1]C[1] ?

[halaster08]

Je connais mal la notation relative: RC[1], c'est bien R[1]C[1] ?

RC[1] = R[0]C[1] = 0 lignes de décalage et 1 colonne de décalage par rapport a la cellule active.
Si la formule est en B6 alors c'est C6

[Daranc]

j'ai fait un test

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub test()
With Sheets(1)
tbl = .Range("j1:k63")
For t = 1 To UBound(tbl, 1)
If tbl(t, 2) <> "" Then
tbl(t, 1) = tbl(t, 2)
End If
Next
.Range("j1:k63").Value = tbl
End With
End Sub

je vous prie de remarquer et de noter l’effort d'imagination pour nommer ce test
la colonne k dans cet exemple est entrée par formule

Code :

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

=si(R1="";"";r1)

j'allais pas trop forcer non plus
en r1 seule quatre cellule contenait un truc (chiffre ou lettre)
ça marche sans problème il est évident qu'on peut aussi faire un redim preserve pour ne réécrire que la colonne j
auquel cas le tableau pose() sera à dimensionné

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
sub test()
dim pose() as variant
With Sheets(1)
tbl = .Range("j1:k63")
For t = 1 To UBound(tbl, 1)
redim preserve pose (1 to t)
If tbl(t, 2) <> "" Then
pose(t) = tbl(t, 2)
else
pose(t)=pose(t,1)
End If
Next
.Range("j1:j63").Value = application.worksheetfunction.transpose(pose)
End With
End Sub

une autre évidence marqué de façon dynamique la taille du range à reposer uniquement en colonne J
PS vérifie le VBA de la deuxieme macros il a été tapé en live

[Polygos]

De toute manière une boucle est inutile lorsqu'une simple formule de calculs peut déjà accomplir le même besoin
et ce, même en VBA !

___________________________________________________________________________________________________________

Je suis Paris, London, Manchester, Egypte, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

Bien d'accord mais le but c'est de s'affranchir des formats et des formules et avoir une cellule réellement vide. c'est pour ça

[Marc-L]

Bien d'accord mais le but c'est de s'affranchir des formats et des formules et avoir une cellule réellement vide. c'est pour ça

J'ai supprimé de suite mon post pensant que tu n'aurais pas eu le temps de le lire …

Mais en fait je maintiens : une formule correctement pensée - ou une fonctionnalité interne d'Excel - s'avère souvent bien plus efficace
qu'une boucle en VBA Général et ce en beaucoup moins de lignes de code !
Un exemple récent : la boucle de 10 bonnes minutes pour supprimer des lignes sous condition alors que le B-A-BA d'Excel ~ 30 secondes …

[Polygos]

Bonjour,

penser excel ! et optimiser les interactions

1) neutraliser les évènements et rafraichissement d'écran
2) sur l'ensemble de la colonne 121, injecter la formule en colonne 122 "=SI(MaCelluleColonne121<>0;MaCelluleColonne121;"")" et remplacer la formule par les valeurs

pour illustrer (et à adapter), je l'ai fais en R1C1 mais on peut le faire en formule référentielle

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Application.ScreenUpdating = false
Application.EnableEvents = false
Application.Calculation = xlCalculationManual
 
With Range(Cells(PL,121),Cells(DL,121))
    .FormulaR1C1 = "=IF(RC[1]<>0,RC[1],"""")"
    .Value = .Value
End With
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = true
Application.EnableEvents = true

Ca, ça fonctionne.

Mais après quand ej veux relire les cellules de al colonne 121 et leur appliquer le test

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
 
wb1.activate
For i = PL To DL
 
        If ws1.Cells(i, 121) >= 1 Then
 
 
        'Colonne A = 1 "item_sku"
            ws2.Cells(j, 1) = ws1.Cells(i, 1)
...
etc..pour les autres solonnes
 
 
j = j + 1
 
        Else
 
        End If
 
Next i

ça ne fonctionne pas; TOUTES les cellules sont prises en compte et pas seulement celles dont la valeur est supérieure ou égale à 1 !

[joe.levrai]

Bonjour,

étrange, la chaine vide "" ne peut pas être >1 ... à la rigueur qu'elle puisse être (dans certains cas précis) considérée comme =0 pourquoi pas/
peux-tu joindre un classeur exemple (format xlsx) avec un jeu de données où ça ne fonctionne pas ?


oublie les boucles, et utilises un filtre avancé si tu veux exporter des lignes basées sur un critère de valeurs

[Invité]

Bonjour Joe,
oui une chaine vide est égale à zéro mais ça cadre avec son test non?
If ws1.Cells(i, 121) >= 1 Then

[Polygos]

Bonjour,

penser excel ! et optimiser les interactions

1) neutraliser les évènements et rafraichissement d'écran
2) sur l'ensemble de la colonne 121, injecter la formule en colonne 122 "=SI(MaCelluleColonne121<>0;MaCelluleColonne121;"")" et remplacer la formule par les valeurs

pour illustrer (et à adapter), je l'ai fais en R1C1 mais on peut le faire en formule référentielle

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Application.ScreenUpdating = false
Application.EnableEvents = false
Application.Calculation = xlCalculationManual
 
With Range(Cells(PL,121),Cells(DL,121))
    .FormulaR1C1 = "=IF(RC[1]<>0,RC[1],"""")"
    .Value = .Value
End With
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = true
Application.EnableEvents = true

J'avais appliqué ta formule:

Code :

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

1
2
3
4
5
6
7
8
9
10
If MsgBox("voulez-vous mettre le stock à jour ?", vbYesNo, "Confirmation") = vbYes Then
 
    With Range(Cells(PL, 121), Cells(DL, 122))
    .FormulaR1C1 = "=IF(RC[1]<>0,RC[1],"""")"
    .Value = .Value
   End With
Else
End If
 
MsgBox "Stock à jour"

et je me retrouve avec la colonne 122 qui prend les valeurs de la 123 et la 121 qui se vide..je vois pas où est l'erreur; en notation relative, on est bien RC1 en 122; mais alors pourquoi .value = .value ?

[Invité]

Bonjour,

If Cells(i, 122) <> 0 Then on change que si c'est différent!

Code :

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

1
2
3
4
5
6
7
8
Sub test()
PL = 10: DL = 1000
Range(Cells(PL, "DQ"), Cells(DL, "DQ")).Insert
Range(Cells(PL, "DQ"), Cells(DL, "DQ")).Value = Range(Cells(PL, "DR"), Cells(DL, "DR")).Value
Range(Cells(PL, "DR"), Cells(DL, "DR")).FormulaR1C1 = "=IF(RC[+1]<>0,RC[+1],RC[-1])"
Range(Cells(PL, "DR"), Cells(DL, "DR")).Value = Range(Cells(PL, "DR"), Cells(DL, "DR")).Value
Range(Cells(PL, "DQ"), Cells(DL, "DQ")).Delete
End Sub

[Polygos]

Bonjour,

If Cells(i, 122) <> 0 Then on change que si c'est différent!

Code :

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

1
2
3
4
5
6
7
8
Sub test()
PL = 10: DL = 1000
Range(Cells(PL, "DQ"), Cells(DL, "DQ")).Insert
Range(Cells(PL, "DQ"), Cells(DL, "DQ")).Value = Range(Cells(PL, "DR"), Cells(DL, "DR")).Value
Range(Cells(PL, "DR"), Cells(DL, "DR")).FormulaR1C1 = "=IF(RC[+1]<>0,RC[+1],RC[-1])"
Range(Cells(PL, "DR"), Cells(DL, "DR")).Value = Range(Cells(PL, "DR"), Cells(DL, "DR")).Value
Range(Cells(PL, "DQ"), Cells(DL, "DQ")).Delete
End Sub

comment je peux ralentir cette exécution au fait ?

[Invité]

dans ton poste de départ, tu parles d'une bête de boucle de 8 minute!

[Polygos]

dans ton poste de départ, tu parles d'une bête de boucle de 8 minute!

oui, j'explique: ma première idée a bien été d'utiliser des formules eExcel, mais c'est parce que ça ne marchait pas que je suis parti sur une boucle VBA

[Invité]

Bonjour,

C'est bien ce que je fais dans l'animation proposé plus haut!

Le problème c'est que vraisemblablement ta formule.ne tient pa comte des "" ce qui les transforme en 0!

Code :

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

 formule= si(A1="";"";A1-10)