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

ChTi160

XLDnaute Barbatruc
Salut KIM
bonjour Bebere
bonjour le Forum
en pièce jointe une autre version je récupère les formules dans un tableau et ensuite pour coller le résultat de la formule j'utilise
TabResult(Lgn - 5, C1) = Evaluate(Application.WorksheetFunction.Substitute(Tableau(1, C1), 2, Lgn))
mais comme dit Bebere il faut voir les autres type de formule
Bonne fin de journée [file name=prjKIMv47.zip size=36563]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv47.zip[/file]

Message édité par: Chti160, à: 20/05/2006 17:16
 

Pièces jointes

  • prjKIMv47.zip
    35.7 KB · Affichages: 27

KIM

XLDnaute Accro
Salut JeanMarie,
Bonjour Bebere et le forum,
Merci de votre collaboration pour la recherche d'une solution generaliste pour l'optimisation de la recopie et l'exec de formules en memoire.
Ci-joint un fichier dans lequel j'ai mis les formules les plus utilisées pour la mise en forme et le calcul dans mes tableaux.
Qu'en pensez-vous?
Est-ce possible d'appliquer l'une de vos methodes.
De ma part je vais faire des tests
Bien amicalement
KIM
 

KIM

XLDnaute Accro
Bonjour JeanMarie, Bebere et le forum,
Avec mes excuses,
Ne rigolez pas trop,
Quand on est débordé, on fait des betises.
Ci-joint un exemple de formules que j'utilise.
Merci de votre avis
Bien amicalement
KIM [file name=formules_used.zip size=2681]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/formules_used.zip[/file]
 

Pièces jointes

  • formules_used.zip
    2.6 KB · Affichages: 25

KIM

XLDnaute Accro
Bonjour JeanMarie, Bebere, PierreJean et le forum,
Sans ouvrir un nouveau fil, je reviens vers vous pour poser une question concernant le fichier de ce fil prjKIM et la recopie de formules par VBA.
Est-il possible de remplir les cellules de la plage H2:O2 par VBA? c-à-d
A la place d'ecrire mes formules dans les cellules de la plage H2:O2,
je les ecris dans une macro VBA qui elle les recopie dans chaque cellule correspondante.
et à la fin des calculs à executer, vider la plage H2:O2.
Cela me permet de definir tous mes calculs en VBA et de fournir une feuille propre sans formules aux utilisateurs finaux.
Merci de votre aide
Bien amicalement
KIM
 

ChTi160

XLDnaute Barbatruc
Salut Kim
bonjour le fil
Bonjour le Forum

je dois partir au boulot mais je regarderai
je ne peux pas te dire comme cela, mais pourquoi pas ,puisque l'on arrive à récupérer les formules dans un tableau et ensuite les coller dans des cellules
donc si personne ne passe par la je verrai cela ce soir
bonne journée

Message édité par: Chti160, à: 01/06/2006 12:02
 
B

bebere

Guest
bonjour Kim,Jean-Marie
je pense que tu devrais mettre un extrait des données auxquelles se rapportent les formules
que tu utilises
changer ces formules en employant les mêmes noms pour les plages,etc

à bientôt
 

KIM

XLDnaute Accro
Bonjour Bebere, et le forum,
Salut JeanMarie,
Ci-joint un fichier de données qui est le meme qu'à l'origine de ce fil. les formules utilisées sont dans H2:O2 que je recopie rapidement dans la plage H5:O29 suite à votre aide dans ce fil.
Pour éviter de laisser trainer des formules dans la fuille DATA2, j'ai souhaité les definir dans une macro, les recopier dans chaque cellule H2:O2 avant de lancer le traitement des données,
et à la fin, de vider la plage H2:O2
Est-ce possible?
Merci d'avance
Bien amicalement
KIM [file name=prj_KIMv1.zip size=30030]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv1.zip[/file]
 

Pièces jointes

  • prj_KIMv1.zip
    29.3 KB · Affichages: 25

pierrejean

XLDnaute Barbatruc
bosoir KIM

est-ce que quelque chose comme cela te conviendrait

il est possible de parametrer les adresses des cellules contenant les formules [file name=prj_KIMv1_20060601173343.zip size=32530]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv1_20060601173343.zip[/file]
 

Pièces jointes

  • prj_KIMv1_20060601173343.zip
    31.8 KB · Affichages: 23

ChTi160

XLDnaute Barbatruc
Salut KIM
bonsoir pierrejean
en piece jointe le fichier de pierrejean avec l'ajout d'une macro qui emmagazine les formules dans un tableau et qui les recopie dans H2:02

a voir
bonne fin de Soirée [file name=prj_KIMv2_01062006.zip size=34481]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv2_01062006.zip[/file]

Message édité par: Chti160, à: 01/06/2006 23:16
 

Pièces jointes

  • prj_KIMv2_01062006.zip
    33.7 KB · Affichages: 22

KIM

XLDnaute Accro
Salut JeanMarie, Bonjour PierreJean, Bebere et le forum,
Effectivement c'est ce que je cherchais,
@Pierrejean, merci de ta reponse initiale qui a été complétée par JeanMarie.
Effectivement il faut mettre les formules dans un tableau avant de les recopier car si on vide la plage initiale H2:O2 je ne peux plus recuperer maes formules.
@JeanMarie, Pour poursuivre ton idée, comment faire pour copier directement les formules dans la plage cible H5:0,
et seulement les valeurs sans passer par H2:O2
Merci encore
Bien amicalement
KIM
 
B

bebere

Guest
Bonjour Kim, JeanMarie, PierreJean, le forum
le changement demandé
à bientôt [file name=prjKIMv3_20060602103431.zip size=34022]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv3_20060602103431.zip[/file]
 

Pièces jointes

  • prjKIMv3_20060602103431.zip
    33.2 KB · Affichages: 21

pierrejean

XLDnaute Barbatruc
re KIM

Salut Chti160
Salut bebere

pas encore regardé ton fichier bebere mais pas le temps maintenant

je termine juste ce que j'avais concocté
la tactique est la suivante:
copier coller en feuille Formules dans la colonne B les formules en mettant a jour si necessaire les references de la colonne A et lancer la copie en feuille DATA2

on pourrait même apres faire un fichier copie de la seule feuille DATA2 [file name=prj_KIMv1_20060602141031.zip size=37928]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv1_20060602141031.zip[/file]
 

Pièces jointes

  • prj_KIMv1_20060602141031.zip
    37 KB · Affichages: 23

Discussions similaires

Réponses
7
Affichages
292

Statistiques des forums

Discussions
311 720
Messages
2 081 917
Membres
101 839
dernier inscrit
laurentEstrées