Discussion :

[TonyNiort79]

Bonjour,
j'utilise un programme en VBA pour recopier des formules toutes simples, pour après une fois terminé compter les doublons les plus sortis,
ci-dessous une image de mon tableau et le code qui fonctionne très bien sur une petite plage, mais qui est trop long en exécution pour ce que je veux faire,

je l'ai lancé hier soir avec un pas de 10, et arrêté ce matin,
puis j'ai modifié le pas de 10 en 1 ce matin, et arrêté à l'instant,

le problème est le suivant :
une colonne de 184 756 lignes qui contiennent une formule type en A22 (pour information les lettres sont des références de colonnes),

Code :

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

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10

je copie la colonne sur la droite, une première fois en formule, puis une deuxième fois en tant que valeur pour gagner de la mémoire,
puis je recommence de gauche à droite pour un total de 14 484 colonnes.
y-a-t-il une autre solution pour ce fichier trop grand ?
ou bien dois-je le scinder en plusieurs éléments ?
merci pour votre aide.

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
Sub CopieFormules()
    With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
            .Interactive = False
    End With
 
    Sheets("AscDiz").Select
    Dim compteur As Long
    Dim i As Long
 
    i = 1
    For compteur = 1 To 184756
        Range("A22:A184777").Copy
        Range(Cells(22, 2), Cells(22, 2 + i)).PasteSpecial Paste:=xlPasteFormulas
        Range(Cells(22, 2), Cells(22, 2 + i)).Copy
        Range(Cells(22, 2), Cells(22, 2 + i)).PasteSpecial Paste:=xlPasteValues
        i = i + 1
    Next compteur
 
    Application.CutCopyMode = False
    Range("A1").Select
 
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Interactive = True
    End With
 
End Sub

Pièce jointe 503196

[Philippe Tulliez]

Bonjour,
Il n'y a aucune raison d'utiliser une boucle pour écrire une formule sur plusieurs lignes

A lire ces deux billets sur le sujet

• Ecrire une formule dans Excel à l'aide d'une procédure VBA
• Comment écrire une formule plus complexe dans une cellule ?

Et pour remplacer une formule par la valeur qu'elle renvoie, il y a plus simple qu'un Copier/Collage spécial-Valeur
A lire ce billet VBA Excel - Comment remplacer des formules par leur résultat ?

[TonyNiort79]

Bonjour,
merci pour votre réponse,
je vais étudier les 3 liens.

[TonyNiort79]

Bonjour,
Il n'y a aucune raison d'utiliser une boucle pour écrire une formule sur plusieurs lignes

A lire ces deux billets sur le sujet

• Ecrire une formule dans Excel à l'aide d'une procédure VBA
• Comment écrire une formule plus complexe dans une cellule ?

Et pour remplacer une formule par la valeur qu'elle renvoie, il y a plus simple qu'un Copier/Collage spécial-Valeur
A lire ce billet VBA Excel - Comment remplacer des formules par leur résultat ?

Bonjour à tous,
tout d'abord, j'avais fait une erreur sur le compteur de la boucle,
j'avais mis 184756 qui correspondent aux lignes alors que je copiais de colonnes en colonnes

je viens d'étudier les liens cités ci-dessus,
et j'ai refait ma procédure,
en enlevant les "=" (égale) sur les formules (soit 184756 au total), mon fichier avait déjà perdu un tiers de sa taille, et s'ouvre beaucoup plus vite
par contre je suis obligé de faire quand même une boucle pour copier ligne par ligne,
d'une part parce que cela prends trop de mémoire, et d'autre part il n'y a pas 2 formules identiques sur les 184756 lignes,
je lance mon programme ce soir, et je vous tiendrai informés.
je pense que cela ira très très bien.

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 CopieFormulesRev1()
    Dim compteur As Long
    Dim i As Long
    i = i + 0
With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
            .Interactive = False
End With
 
    For compteur = 1 To 184756 ' Ligne 22 à Ligne 184777
        With ThisWorkbook.Worksheets("AscDiz")
         .Range(Cells(22 + i, 1), Cells(22 + i, 14485)).Formula = "=" & Cells(22 + i, 1)  ' insertion de la formule inscrite sur colonne A
         .Range(Cells(22 + i, 1), Cells(22 + i, 14485)).Value = .Range(Cells(22 + i, 1), Cells(22 + i, 14485)).Value    ' copie en tant que valeur
        End With
        i = i + 1
    Next compteur
 
Range("A1").Select
 
With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Interactive = True
End With
End Sub

[Philippe Tulliez]

Bonjour,

par contre je suis obligé de faire quand même une boucle pour copier ligne par ligne,
d'une part parce que cela prends trop de mémoire, et d'autre part il n'y a pas 2 formules identiques sur les 184756 lignes,

Il n'est pas normal d'avoir des lignes avec des formules différentes dans une liste de données. Il faut réfléchir à une formule qui réponde à tous les cas

[eriiic]

Bonjour à tous,

ton .Value=.Value, tu devrais le faire sur la plage complète en une fois.
Tu gagneras des milliers d'écritures pour pas cher.
De la même façon, tu pourrais lire en une fois les variables d'entrées dans une variable tableau, calculer en vba le résultat dans un autre tableau que tu colles en une fois à la fin. Même plus besoin du .Value=.Value
Tu tomberas à qq minutes...
Bon, si tu as 14000 colonnes à faire, tu peux découper par tranche de 1000, ça ne fera que 14 écritures.
eric

[TonyNiort79]

Bonjour,

Il n'est pas normal d'avoir des lignes avec des formules différentes dans une liste de données. Il faut réfléchir à une formule qui réponde à tous les cas

Bonjour,
le programme que j'avais lancé hier soir à 21h00, je viens de l'arrêter,
en regardant le gestionnaire des tâches, la mémoire était arrivée à 98% (en haut du tableau) avec une taille qui arrivait à 128 000 Mo,

en ce qui concerne les formules différentes elles se trouvent uniquement sur la première colonne ("A"), ce sont des combinaisons

Code :

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

=combin(20;10)

le résultat donne 184756 combinaisons que je devais copier sur la totalité des colonnes,
puis compter les doublons avec le programme de Jacques Boigontier en utilisant le Dictionary d'Excel.

[TonyNiort79]

Bonjour à tous,

ton .Value=.Value, tu devrais le faire sur la plage complète en une fois.
Tu gagneras des milliers d'écritures pour pas cher.
De la même façon, tu pourrais lire en une fois les variables d'entrées dans une variable tableau, calculer en vba le résultat dans un autre tableau que tu colles en une fois à la fin. Même plus besoin du .Value=.Value
Tu tomberas à qq minutes...
Bon, si tu as 14000 colonnes à faire, tu peux découper par tranche de 1000, ça ne fera que 14 écritures.
eric

Bonjour,
Avant de passer par .Value=.Value, je dois passer par la copie des formules, et c'est parce que il y a trop de formules actives que le programme est lent,
surtout au fur et à mesure que le tableau augmente colonne par colonne.

Oui, c'était ma première idée, de scinder le tableau en plusieurs fichiers et copier par pavés de 500 Lignes x 500 Colonnes

[eriiic]

Bonjour,

je dois passer par la copie des formules

Non, tu n'es pas obligé. Tu peux faire tes concaténations sur des tableaux entiers et coller tout d'un coup.
Ecrire une par une des dizaines de milliers de cellules 2 fois, tu n'arriveras jamais à le faire rapidement.
eric

[Theze]

Bonjour,

Eric a parfaitement raison , travailler sur une si grande plage doit se faire sur un tableau en mémoire pour un gain significatif de temps !
Maintenant, si tu persistes dans ta façon de faire, tu peux dans ce cas empêcher le calcul automatique en début de code et le rétablir à la fin tu gagnera un peu
C'est quoi cette instruction :

Code :

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

1
2
 
i = i + 0

Toutes les variables numériques sont initialisées à 0 au lancement de la procédure donc 0 + 0 = 0

[TonyNiort79]

Bonjour à tous,

j'avais déjà essayé de copier en une seule fois,
mais Excel se fige pendant des heures,

ce matin, j'ai lancé la procédure sur 10 lignes,
puis 100 lignes, cela mets environ 1mn30,
en tout 5 fois et j'ai copié le résultat sur un nouveau classeur,
soit un total de 510 lignes, ce qui donne un fichier d'une taille de 107 Mo, et il me reste à extraire encore 178000 Lignes,
oui, je sais, c'est un truc de malade, mais je ne me rendrais pas aussi facilement,

Grâce à Philippe Tulliez, j'ai éliminé l'activation des formules en enlevant le signe "=",
ce qui me permet d'ouvrir mon fichier et d'exécuter les procédures beaucoup plus rapidement
et Grâce à Jacques Boigontier, de lister la plage sur une colonne,
à propos Jacques, en copiant ta procédure sur mon fichier, le programme bloque sur Mondico "Objet requis"
mais si je prends ton fichier Classeur1-1.xlsm, et j'y ajoute mes données, le programme ne bloque pas.

https://www.developpez.net/forums/d2.../#post11133966



je vais essayer de m'y prendre autrement,
je vais extraire ligne par ligne, et l'envoyer directement dans une base Access,

Merci à tous.

[TonyNiort79]

Bonjour,

Eric a parfaitement raison , travailler sur une si grande plage doit se faire sur un tableau en mémoire pour un gain significatif de temps !
Maintenant, si tu persistes dans ta façon de faire, tu peux dans ce cas empêcher le calcul automatique en début de code et le rétablir à la fin tu gagnera un peu
C'est quoi cette instruction :

Code :

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

1
2
 
i = i + 0

Toutes les variables numériques sont initialisées à 0 au lancement de la procédure donc 0 + 0 = 0

oui, effectivement, j'ai fait une petite erreur en déclarant la valeur de i = 0,
sans m'en rendre compte j'avais écrits i = i + 0,
et cela revient au même.
et comme qui dirait l'autre, "seul le résultat compte"
à propos qui a dit cela ? je ne sais pas

[Theze]

Bonjour,

le programme bloque sur Mondico "Objet requis"

Le dictionnaire doit être déclaré en objet donc, c'est :

Code :

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

1
2
 
Dim Mondico As Object

et non :

Code :

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

1
2
 
Dim Mondico As String

[eriiic]

Bonjour à tous,

Code :

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

For Each c in Range(...)

tu continues à lire presque 3 millions de cellules, cellule par cellule. Normal que ça reste très lent.

Un exemple. Tu as 3 colonnes de données en A:B à partir de la ligne 2 et tu veux 2 colonnes de résultat en E:F :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub test()
    Dim datas, result(), lig As Long, col As Long
    ' lecture par plage complète
    datas = [A2:C2].Resize(Cells(Rows.Count, 1).End(xlUp).Row).Value
    ' traitement en mémoire, on utilise le tableau datas au lieu de la feuille
    ReDim result(1 To UBound(datas), 1 To 2)
    For lig = 1 To UBound(datas)
        result(lig, 1) = datas(lig, 1) & datas(lig, 2)
        result(lig, 2) = datas(lig, 2) & datas(lig, 3)
    Next lig
    ' écriture par plage complète
    [E2].Resize(UBound(result, 1), UBound(result, 2)) = result
End Sub

teste sur 180000 lignes et tu auras une idée du gain.
eric

PS : si tu utilises trop de mémoire, excel va utiliser la mémoire virtuelle extrèmement lente (sur le disque dur). Dans ce cas travaille par blocs de données (boucle pour x colonnes) pour découper le travail.

[MarcelG]

Bonjour à vous, et Bonjour au Forum,

J'ai eu plusieurs échanges à ce sujet, notamment avec l'ami mercatog.

Pour ma part, je reste sur une déclaration en

Code :

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

Scripting.Dictionary

.
(Ne pas omettre d'activer alors la Référence

Code :

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

Microsoft.Scripting Runtime

)

Cette déclaration permet, notamment, une meilleure appréhension des méthodes et propriétés.

[TonyNiort79]

Bonjour à tous,

effectivement, cela fonctionne avec la déclaration comme object,
et je pense que la différence entre les 2 fichiers est que dans mon fichier je dois obligatoirement déclarer les variables avec Option Explicit
merci pour l'aide

je viens de faire un essai avec 14 000 ligne, et c'est du rapide,
je vais étudier cela, comprendre et essayer de l'adapter
merci beaucoup.

merci aussi pour la réponse,
mais je maitrise encore moins le dictionary, même si j'ai compris un tout petit peu.