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]
 

Pièces jointes

  • prjKIMv1.zip
    35.1 KB · Affichages: 104

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]
 

Pièces jointes

  • prjKIMv2.zip
    42.6 KB · Affichages: 124

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
 

Pièces jointes

  • prjKIMv1_20060504194943.zip
    26.7 KB · Affichages: 121

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]
 

Pièces jointes

  • prjKIMv3.zip
    46.3 KB · Affichages: 108

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]
 

Pièces jointes

  • prjKIMv1_20060505085615.zip
    30.8 KB · Affichages: 100

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]
 

Pièces jointes

  • prjKIMv4.zip
    49.8 KB · Affichages: 115

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
 

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
 

Discussions similaires

Réponses
7
Affichages
292

Statistiques des forums

Discussions
311 705
Messages
2 081 733
Membres
101 807
dernier inscrit
foued