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

KIM

XLDnaute Accro
Re,
Avec mes excuses, c'est la premiere fois que ça m'arrive.
@Pierrejean,
faute de na pas pouvoir joindre le fichier,
c'est toujours le meme fichier prj_KIMv11.xls
je n'ai gardé que 3 formules dans la feuille Formules: celles de H5, J5 et N5
Celle de H5 ne concerne que la colonne H, donc pas de recopie à droite.
Celle de J5, la recopier jusqu'à la col M c-a-d 3 col à droite,
celle de N5, la recopier jusquà finH derniere col non vide de la ligne 4
et ensuite on recopie vers le bas l'ensemble de la ligne H
Merci d'avance
Bien amicalement
KIM
 

ChTi160

XLDnaute Barbatruc
Salut KIM
bonjour pierrejean,Bebere
je n'ai pas le fichier prj_KIMv11.xls
donc comme tyu le verras je n'ai que deux macro l'une pour H5 et l'autre qui remplissait les autres colonne soient I à O donc j'ai réalisé ce que tu demande (je pense lol)mais en collant deux fois la même formule dans J5 et N5
enfin tu regardes
voila la macro
Sub recupFormule_2()
Dim Tableau() As Variant
ReDim Tableau(2)
Application.ScreenUpdating = False
Tableau(0) = '=' & 'SOMMEPROD((ColD=$G5)*ColB)'
Tableau(1) = '=' & 'SOMMEPROD((ColD=$G5)*(ColA=I$4)*ColB)'
Tableau(2) = '=' & 'SOMMEPROD((ColD=$G5)*(ColA=J$4)*ColB)' 'j'ai remis la même car je n'ai pas le fichier _
avec les trois macros différentes de H5, J5 et N5 LOL il te suffit de changer la formule de Tableau(2)

For L = 0 To UBound(Tableau, 1)

Select Case L
Case Is = 0
Range('H5').FormulaLocal = Tableau(L)
Case Is = 1
Range('I5').FormulaLocal = Tableau(L)
Range('I5').AutoFill Destination:=Range('I5:L5'), Type:=xlFillDefault
Case Is = 2
Dim Derlgn As Byte ''je n'ai pas compris quelle formule mettre en M
Dim DerCol As Byte

DerCol = Range('IV4').End(xlToLeft).Column
Range('M5').FormulaLocal = Tableau(L)
Range('M5').AutoFill Destination:=Range(Cells(5, 13), Cells(5, DerCol)), Type:=xlFillDefault
End Select
Next
Derlgn = Range('G65536').End(xlUp).Row
Range(Cells(5, 8), Cells(5, DerCol)).AutoFill Destination:=Range(Cells(5, 8), Cells(Derlgn, DerCol)), Type:=xlFillDefault
Range(Cells(5, 8), Cells(Derlgn, DerCol)).Value = Range(Cells(5, 8), Cells(Derlgn, DerCol)).Value

Application.ScreenUpdating = True
End Sub
bonne fin de jOurnée [file name=prj_KIMv_080606.zip size=43776]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv_080606.zip[/file]
 

Pièces jointes

  • prj_KIMv_080606.zip
    42.8 KB · Affichages: 88

pierrejean

XLDnaute Barbatruc
bonjour tous

voila une ultime KIMv11

a partir de ce qui existait:

on part avec la DATA2 vierge, la colonne A de la feuille formule vide a partir de la ligne 2

apres la preparation (ligne 4 et colonne G de la feuille DATA) on passe a la feuille formule ou en colonne A on trouve les adresses ou mettre les formules).On peut ne pas remplir toute la colonne B
ensuite retour a calcul et enregistrement

note :les boutons sont 'volants' et permettent de se deplacer parmi les feuilles
 

pierrejean

XLDnaute Barbatruc
oups probleme de taille

ablation de DATA3 pour reduire a 50k [file name=prj_KIMv11_20060608160656.zip size=49292]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv11_20060608160656.zip[/file]
 

Pièces jointes

  • prj_KIMv11_20060608160656.zip
    48.1 KB · Affichages: 94

ChTi160

XLDnaute Barbatruc
Salut pierrejean

je te remercie pour toutes ces procèdures qui me permettent d'apprendre ,comment résoudre les problèmes, c'est ce qui est interressant avec VBA c'est que chacun peut avoir une vision des choses différentes et arriver au même résultat
donc merci,c'est très enrichissant
bonne fin de Journée
 

KIM

XLDnaute Accro
Salut les amis de ce fil, et le forum
Merci à vous tous,
j'ai appris beaucoup de choses.
@Jeanmarie,
J'ai adapté ta methode à différents cas de figures sur mes données reelles et tout marche Merci.
@Pierrejean,
Ta methode est bien differente et je souhaite aussi l'utiliser. Je viens de recevoir ton dernier fichier. Il me faut du temps d'assimiler le code. Je vais regarder tout et vous tiendrai au courant
Une idee (je deraille peut etre), d'envisager une 3eme col C avec le nombre de col à recopier
0, 1, 2, etc.. ou n pour dire jusqu'a la fin de la derniere col,
@Bebere merci à toi aussi.
Comme disait Jeanmarie, les methodes sont différentes, enrichissantes et le resultat est le meme. Reste le temps d'execution à controler sur des grands tableaux de plus de 50 000 lignes et 60 colonnes
Je suis absent dès ce soir, je déménage mon fils ce WE de Metz à ClermontFerrand en repassant par Paris
Bon WE
Bien amicalement
KIM
 

Bebere

XLDnaute Barbatruc
bonsoir
Jean-Marie n'es-tu pas un peu tôt pour le we (jeudi),j'ai pas beaucoup le temps(bécause beau temps et oust dehors),mais je suis les ébats(heu, les débats),très très intéressant
Pierre-jean on se ressemble lunette moustache à la gaulloise cheveu court,etc....?,peut être en âge,la différence amitiés René
 

ChTi160

XLDnaute Barbatruc
Arffffff Bebere

moi je vais te dire jusqu'a 14h00 j'ai cru qu'on était Vendredi
mais moi j'ai plus l'habitude d'être en WeekEnd les jours de Semaine que les Samedi Dimanche d'ou ton étonnement lol

j'espère que tu profites de nos ébats Lol
moi j'apprends et c'est super
bonne fin de Semaine
 

KIM

XLDnaute Accro
Bonjour Jeanmarie, Pierrejean, Bebere et le forum,
mon WE ne commence jamais un vendredi, mais pour le déménagement de mon fils j'ai dû prendre mon vendredi et vous souhaiter un bon WE car j'allais être déconnecté ces 3 derniers jours.
Le déménagement s'est tres bien passé Dieu merci , mais fatiguant.
Grâce à vous j'ai beaucoup avancé avec ce fil dans mon projet. J'ai appliqué les methodes des une et des autres car dans chaque contribution il y a une ou plusieurs nouveautés.
1- Recopie formules:
J'ai finalisé mon projet avec la methode de Jeanmarie. Je souhaite maintenant utiliser la methde de Pierrejean.
@Pierrejean,
Dans ton dernier message et avec la superbe nouveauté de créer automatiquement la colonne A de la feuille 'Formules', tu disais
'On peut ne pas remplir toute la colonne B'
Dois-je comprendre que s'il y a une ligne vierge entre 2 forumules la macro recopie la 1ere formule sur 2 col, et la derniere formule il recopie jusqu'à la derniere col de la ligne G? J'essaye de comprendre comment tu gères le nombre de col à recopier pour chaque formule?
Merci d'avance si tu peux me commenter ton nouveau code.

2- copie de plages
Pour cible1, grâce à vous, c'est réglé,
et pour cible2 j'ai utilisé la macro ci-dessous comme l'a suggéré Jeanmarie et ça marche.
Sub copyplage_img()
myclas = ActiveWorkbook.Name
Application.Calculation = xlCalculationManual
Workbooks(myclas).Worksheets('CIBLE').Range('ed_tablo').Copy
Workbooks(myclas).Worksheets('CIBLE_2').Range('a1').PasteSpecial Paste:=xlPasteValues
Workbooks(myclas).Worksheets('CIBLE_2').Range('a1').PasteSpecial Paste:=xlPasteFormats

.... autant de plages nommées à recopier

Application.CutCopyMode = False
End Sub

Merci à vous et au forum
Bien amicalement
KIM
 

pierrejean

XLDnaute Barbatruc
bonjour KIM , JeanMarie, Bebere, le forum

@KIM voila la version avec des blancs dans la colonne B , la formule etant dans ce cas reprise de la ligne precedente et poursuivie jysqu'en fi de colonne

elle arrive un peu tard car j'ais du la reconstituer (une 1ere version doit etre quelque part perdue dans les bas-fonds de mon DD)

je continue a y travailler dans une optique globale (ne demander que les tetes de colonne et nommer dynamiquement les plages utilisées).
entre temps si tu le souhaites je peux agrémenter les macros de commentaires [file name=prj_KIMv11_20060612153343.zip size=50695]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv11_20060612153343.zip[/file]
 

Pièces jointes

  • prj_KIMv11_20060612153343.zip
    49.5 KB · Affichages: 99

Discussions similaires

Réponses
7
Affichages
292

Statistiques des forums

Discussions
311 740
Messages
2 082 049
Membres
101 882
dernier inscrit
XaK_