Recopie ligne formules par vba et optimisation

KIM

XLDnaute Accro
Bonjour le forum.
J'utilise souvent la technique ci-dessous:
Je definis mes formules sur une ligne
et je la recopie par macro dans la plage souhaitée.
Ex:
mes formules en G2:M2 ,
la macro 'Copie_ligneformules_dansplage'
recopie et calcule chaque formule dans la plage G5:M208

Mes données reelles font presque 60 000 lignes et 50 colonnes. et le temps d'exécution est tres tres long.
Je souhaite optimiser ce code et le rendre plus rapide en utilisant les tableaux. Cela me fera gagner beaucoup de temps.
Merci de votre aide
Amicalement
KIM

Sub Copie_ligneformules_dansplage()
Dim w_nfile As String
Dim MyPath As String
Dim Ws As Worksheet
Dim n_line As Long


Set Ws = Worksheets('DATA2')

With Application
.ScreenUpdating = False
' .ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

w_nfile = ActiveWorkbook.Name
MyPath = ActiveWorkbook.Path
Ws.Activate
Ws.Select
If Ws.AutoFilterMode Then
Selection.AutoFilter
End If

Windows(w_nfile).Activate
ActiveSheet.Calculate
n_line = Ws.Range('E2')

Range('G5:M' & n_line).Select
Selection.ClearContents


Range('G2:M2').Copy Destination:=Range('G5:G' & n_line)
ActiveSheet.Calculate
With Selection
.Copy
.Calculate
' .PasteSpecial Paste:=xlFormats
.PasteSpecial Paste:=xlValues
End With

With Application
.CutCopyMode = False
.DisplayAlerts = False
.ScreenUpdating = True
' .ScreenUpdating = False
.Calculation = xlCalculationManual
End With


MsgBox 'c'est fini'
End Sub [file name=prjKIMv1.zip size=35953]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv1.zip[/file]
 

Fichiers joints

pierrejean

XLDnaute Barbatruc
bonsoir KIM

vois si cette solution te conviens

en feuille3 lancer la macro repartition (module2)
 

Bebere

XLDnaute Barbatruc
bonsoir Kim,Pierre Jean
résultat dans feuil3,le code aussi
vois si cela te convient
à bientôt [file name=prjKIMv2.zip size=43602]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv2.zip[/file]
 

Fichiers joints

pierrejean

XLDnaute Barbatruc
et pour cause!!!

tros gros

voila sans la feuille data [file name=prjKIMv1_20060504194943.zip size=27302]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv1_20060504194943.zip[/file]


et bravo a bebere !!!

ta solution est presque 2 fois plus rapide que la mienne qui l'est environ 4 fois plus que l'original

Message édité par: pierrejean, à: 04/05/2006 19:59
 

Fichiers joints

KIM

XLDnaute Accro
Bonjour PierreJean, Bebere et le forum,
Merci de votre reponse rapide.
Comme tu l'as souligné PierreJean effectivement la solution de Bebere est plus rapide. J'ai essayé de regarder le code de vos macros mais je n'ai rien compris. J'ai beaucoup de lacunes dans l'utilisation des tableaux . Vous-est-il possible de commenter le code de vos macros?
Merci d'avance
Parcontre j'ai regardé un peu le code mais je n'ai pas retrouvé la logique que j'utilise:
J'ai une formule:
- en H2 que je dois recopier de H5 à H208
-en G2 que je dois recopier de G5 àG208
etc...
c-à-d une ligne de formules : de H2 à M2
que je dois recopier dans la plage de H5:M208.
Ces formules sont differentes d'une application à une autre mais le principe est toujours le meme: une formule dans une cellule ou une ligne de formules à recopier dans une plage determinée.
Merci de vos explications et de votre aide.
Bien amicalement
KIM
 

Bebere

XLDnaute Barbatruc
rebonsoir
code commenté
feuil3 formatée
à bientôt [file name=prjKIMv3.zip size=47393]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv3.zip[/file]
 

Fichiers joints

pierrejean

XLDnaute Barbatruc
bonjour KIM

salut Bebere

voici ma version commentée
avec suppression de mise à jour d'ecran je gagne un peu de temps mais pas assez à mon gout
cela tient surement à une sous-utilisation des tableaux par rapport à la version bebere [file name=prjKIMv1_20060505085615.zip size=31574]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv1_20060505085615.zip[/file]
 

Fichiers joints

KIM

XLDnaute Accro
Re, PierreJean, Bebere et le forum,
@ Bebere, Est-il possible de trier la colonne A et la ligne 1 de la feuil3?
Que signifie cette ligne :
ColData.Add CStr(Tablo(i, 1)), CStr(Tablo(i, 1))

@PierreJean, Tu as une approche que Bebere, as-tu le temps de commenter ton code? Cela me permet de le comprendre.

Comme Bebere me l'a dit: le gros avantage avec les tableaux,tout se passe en mémoire. Merci de m'aider à transformer ma ligne de commande en utilisant les tableaux:
Range('G2:M2').Copy Destination:=Range('G5:G' & n_line)
avec n_line = ActiveSheet.Range('A65536').End(xlUp).Row
Si je definis mes tableaux de la maniere suivante:
With Sheets('data2')
'Ligne des cellules contenant des formules
LigneSource = .Range('G2:M2')
'Plage cible pour la recopie de la LigneSource
PlageCible = .Range('G5:M' & .Range('A65536').End(xlUp).Row)
End With
Est-ce correct?
Comment maintenant traduire en code la demarche suivante:
Pour chaque ligne de PlageCible
Copier chaque cellule de LigneSource
dans la cellule correspondante de la ligne PlageCible
Si quelqu'un peut m'aider Merci d'avance
Bien amicalement
KIM
 

KIM

XLDnaute Accro
Re, PierreJean, Bebere et le forum,
@Pierrejean,
nos messages se sont croisés, merci pour les commentaires je vais les lire tout desuite.

Toi, Bebere ou un forumeur pouvez m'aider à transformer ma ligne de commande en utilisant les tableaux:
Range('G2:M2').Copy Destination:=Range('G5:G' & n_line)

voir mon dernier message dans ce fil
Merci encore
Amicalement
KIM
 

Bebere

XLDnaute Barbatruc
bonjour Kim,Pierre Jean,le Forum
ajouter tris et commentaires
corrigé quelques fautes
je regarde ton code
à bientôt [file name=prjKIMv4.zip size=50966]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv4.zip[/file]
 

Fichiers joints

Bebere

XLDnaute Barbatruc
Pierre Jean
si tu remplaçes,exemple: For n = 1 To proj.Count
par for each item in proj.Count
et employer with activesheet end with
je pense que cela va accélérer ton code

à bientôt
 

KIM

XLDnaute Accro
Re, PierreJean, Bebere et le forum,
J'essaye de faire des tests en declarant des tableaux.
En déclarant ma ligne de formules G2:H2 dans un tableau et en la copiant dans G5:M5
j'ai remarqué que dans le tableau des lignes de formules il y a les valeurs des cellules qui sont recopiées et non les formules.
Comment faire ?
Merci d'avance
Amicalement
KIM
 

Bebere

XLDnaute Barbatruc
bonsoir Kim
'en français
tbl = Sheets('data2').Range('G2:M2').FormulaLocal
'en anglais
tbl = Sheets('data2').Range('G2:M2').Formula
à bientôt
 

KIM

XLDnaute Accro
Re, PierreJean, Bebere et le forum,
@Bebere,
J'ai essayé ta formule,
j'ai dans tbl les formules,

Sub TestArray()
Dim tbl As Variant
'renseigner le tableau
tbl = ActiveSheet.Range('G2:M2').FormulaLocal
'restituer le tableau

ActiveSheet.Range('H5:M5') = tbl
ActiveSheet.Range('H6:M6') = tbl
ActiveSheet.Range('H7:M7') = tbl
'etc.. pour toute la plage H5:M208
'j'ai essayé la formule ci-dessous
'sans resultat
' tbl.Copy Destination:=Range('H5:M5')

End Sub

mais je n'arrive qu'à recoier la meme formule sur les lignes H5:M5, H6:M6, H7:M7
sans modification automatique de la forule selon la ligne à recopier comme dans:
Range('G2:M2').Copy Destination:=Range('G5:G' & n_line)

c-à-d: en H6 je dois avoir le resultat de la formule: =SI(G5='';'';SOMMEPROD((CDE)*(CODE=$G6)))

en J6: =SI($H6='';'';SOMMEPROD((CDE)*(CODE=$G6)*(DEP=J$4)))
etc...

Merci de votre aide (voir fichier d'origine)
Amicalement
KIM
 

KIM

XLDnaute Accro
Re, Bebere, PierreJean,et le forum,

Je reviens vers vous car j'ai besoin de votre aide et celle du forum pour resoudre ce probleme et vous en remercie d'avance.

@Bebere je conserve ta macro et je vais l'utiliser dans ce contexte particulier. Mais ma demande est plus generale et concerne tout type de formules.

J'ai essayé d'introduire les tableaux dans mon code (voir macro recopie incrementee, bouton recopie formules V2).
- Je créé la liste unique de projets dans 'G5:G' & derlina
par recopie de la formule cellule G2 dans 'G5:G' & derlina
- Recap Depenses par projet et par departement:
par recopie de la ligne des formules sommeprod cellules H2:M2 dans la plage 'H5:M' & derling

1- Grande deception, aucun gain de temps par rapport à la V1.
2- Comment utiliser cette methode: recopie incrementée de ligne de formules dans une plage donnée en utilisant les tableaux d'une manière optimale
3- derling = ActiveSheet.Range('G65536').End(xlUp).Row
et derlina = ActiveSheet.Range('A65536').End(xlUp).Row
me donnent la meme valeur ce qui est anormal. Pourquoi?
Si quelqu'un peut m'aider? Merci d'avance.
Amicalement
KIM [file name=prjKIMv40.zip size=46058]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv40.zip[/file]
 

Fichiers joints

pierrejean

XLDnaute Barbatruc
bonjour KIM

attention ta version 2 pollue la colonne G
teste la v1 avant et apres la v2 (temps x 4)
reesaie apres avoir tout supprimé en G apres la derniere valeur (+ 1 ligne)
je jete un oeil sur le probleme tel que tu le definis mais je ne te cache pas qu'il y a de fortes (mal)chances pour qu'il s'avere insoluble
 

Bebere

XLDnaute Barbatruc
bonjour Kim
extraction sans doublons et autofill
explication dans feuille
nettoyer fichier pour avoir - de 50k
à bientôt [file name=prjKIMv40_20060508110650.zip size=47871]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv40_20060508110650.zip[/file]
 

Fichiers joints

KIM

XLDnaute Accro
Bonjour Bebere, PierreJean,et le forum,

Merci à vous tous d'avoir regardé mon probleme.
@PierreJean, La macro de Bebere repond bien à ma demande, mais j'ai un souci de format de données quand les codes sont des nombres et à l'origine le format est considéré comme du texte en standard.
@Bebere,
Je viens de tester ta nouvelle macro. Cela repond effectivement à mon probleme. Parcontre j'ai un souci en testant la macro:
1- Pour l'extraction des codes, tous les nombres sont considérés par defaut de type numerique. Le calcul des formules H5:M10 ne donnent que du '0'.
Comment modifier la recopie des codes avec le format d'origine.
2- Est-il possible de creer la ligne des Departement par extraction sans doublons des codes de la colonne A et recopie par transposition à partir de I4 et en H4 le titre Depenses Globales Cela me permet d'automatiser completement le tableau avec:
- Extraction sans doublons des codes dans G5:...
- Extraction sans doublons des Departements dans I4:... avec H4 titre fixe
- Copier H2:M2, Selection H5 et Autofill, copier, calculer et coller valeur
Merci d'avance pour ces modifs
Bien amicalement
KIM
 

Discussions similaires


Haut Bas