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

pierrejean

XLDnaute Barbatruc
re KIM

on ne s'est peur-etre pas compris sur le point2

pour moi il s'agit de la liste des dep qui est bien traitée dans le dernier KIM1

je le remets tout de même [file name=KIM1_20060607110240.zip size=18493]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/KIM1_20060607110240.zip[/file]
 

Pièces jointes

  • KIM1_20060607110240.zip
    18.1 KB · Affichages: 87

pierrejean

XLDnaute Barbatruc
re

avec mise à jour de la liste [file name=KIM1_20060607113504.zip size=20418]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/KIM1_20060607113504.zip[/file]
 

Pièces jointes

  • KIM1_20060607113504.zip
    19.9 KB · Affichages: 92

KIM

XLDnaute Accro
Re, les amis, et le forum,
Je viens de perdre mon fichier de travail avec les données reelles et les differentes macros intégrées et adaptées à ce fichier. Il y a une heure, Filesecure me dit que ce fichier est infecté par un virus inconnu. Je n'arrive plus à le modifier ni à le sauvegarder, ni sauvegarder sous. Je force Excel à quitter et je ne peux ouvrir ce foutou fichier. Je l'ai testé avec Panda antivirus, panda ne voit pas le virus, avec Filesecure, FS le detecte en tant que fichier corrompu par un virus inconnu. ....
Je vais rechercher d'autres solutions...
merci
KIM
 

KIM

XLDnaute Accro
Re,
Enfin, j'ai récupéré mon fichier.
@Jeanmarie,
dans ta macro recupformule, tu definies dans un tableau les differentes formules à utiliser
et tu les copies dans H2:O2
Il m'arrive d'avoir des formules dans des cellules non contigues. Est-il possible de copier dans H5 la formule du tableau(2)
dans K5 celle du tableau(3) et dans K5:M5 celles du tableau(4,5 et 6)

@Bebere
Merci pour la modif
Bien amicalement
KIM
 

ChTi160

XLDnaute Barbatruc
Salut KIM
bonsoir le fil
bonsoir le Forum

en pièce jointe une possibilite avec un select case qui permet d'adapter l'endroit où l'on colle la Formule en fonction de l'indice du tableau [file name=prj_KIMv2_07062006.zip size=34731]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv2_07062006.zip[/file]
 

Pièces jointes

  • prj_KIMv2_07062006.zip
    33.9 KB · Affichages: 88

KIM

XLDnaute Accro
Salut Jeanmarie, le fil et le forum,
@Jeanmarie,
merci de tes explications,
Je me heurte sur un problem de syntaxe depuis 2 heures et je n'arrive pas à trouver la solution.
Il y des fois j'utilise une meme formule pour plusieurs cellules mais avec des references differentes (voir fichier ci-joint)
Ex:
Dans I2, il y a la formule =SOMMEPROD((ColD=$G2)*(ColA=I$4)*ColB)
que j'essaye de declarer dans un tableau tableau(0) = ...

les limiites du tableau final sont calculées par le code ci-dessous (merci au Dream Team)
Dercol = IIf(.Cells(5, 255).End(xlToLeft).Column < 7, 7, .Cells(5, 255).End(xlToLeft).Column)
Derlgn_C = .Range('C65536').End(xlUp).Row
Si je copie à la main I2 dans I5 et je glisse avec la souris I5 à droite jusqu'à la derniere colonne et vers le bas jusquà la derniere ligne, la formule se recopie avec les bonnes references des cellules.
Depuis plus d'une heure j'essaye toutes les combinaisons déjà vue sans résultat.
Est-il possible d'envisager les 2 cas c-dessous que je souhaite utiliser avec une seule formule dans tableau(0):
1- For Col = 7 To Dercol
Range(Cells(6, Col)).FormulaLocal = Tableau(0)
Next Col
me donne un message d'erreur.
2- For Col = 7 To Dercol
For Lgn=5 To Derlgn
Range(Cells(Lgn, Col)).FormulaLocal = Tableau(0)
Next Col
Next Lgn
meme message d'erreur
C'est tres pratique car on n'utilise qu'une seule formule quand c'est possible
Merci d'avance
Bonne nuit
KIM [file name=prj_KIMv31_JM.zip size=32238]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv31_JM.zip[/file]
 

Pièces jointes

  • prj_KIMv31_JM.zip
    31.5 KB · Affichages: 86

Bebere

XLDnaute Barbatruc
bonjour Kim,Pierre-Jean,Jean-Marie,le Forum
moi, je rêvais à des (a3-b3)3(lol)
beau temps,donc jardin(il y a du retard)
si j'ai bien compris solution pièce jointe
à bientôt [file name=prjKIMv31JM.zip size=37617]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv31JM.zip[/file]
 

Pièces jointes

  • prjKIMv31JM.zip
    36.7 KB · Affichages: 85

pierrejean

XLDnaute Barbatruc
bonjour KIM Jean Marie bebere

@bebere

je jetes un cil sur ton travail mais comme j'ais aussi commis quelquechose je le soumets

@KIM
quid de KIM1 ? [file name=prj_KIMv31_JM_20060608100608.zip size=41557]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv31_JM_20060608100608.zip[/file]
 

Pièces jointes

  • prj_KIMv31_JM_20060608100608.zip
    40.6 KB · Affichages: 86

KIM

XLDnaute Accro
Bonjour Pierrejean, Jeanmarie, Bebere et le forum,
Depuis ce matin je n'arrive plus à répondre à ce fil. Comme reponse j'ai erreur su serveur.

@Pierrejean,
Concernant ta macro de recopie de formules,
fichier joint avec commentaires.
merci d'avance
KIM
 

KIM

XLDnaute Accro
Bonjour Pierrejean, Jeanmarie, Bebere et le forum

@Pierrejean,
Dans ton code tu prevois les forules dans une feuille 'Formules'
qu'on peut effectivement cachée à l'utilisateur final.
Quand les formules sont similaires, manuellement on crée la formule dans une celleule
et on la recopie à droite ou en bas et les references se mettent à jour automatiquement.
1- Est-il possible d'appliquer cette methode dans ton code?
Dans le fichier joint, j'ai conservé seulement 3 formules pour H5, J5, et N5
Dans ta macro, après avoir recopié les formules dans les cellules correspondantes,
comment on peut rajouter les cas ci-dessous:
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
 

Discussions similaires

Réponses
7
Affichages
292

Statistiques des forums

Discussions
311 720
Messages
2 081 907
Membres
101 836
dernier inscrit
karmon