Remplacer des formules par VBA

[Aloha]

Bonjour,

Pour chaque personne dans une liste je calcule une feuille avec plein de valeurs, puis je copie les valeurs personne par personne dans un autre classeur.
Etant donné qu'il y a beaucoup de formules c'est assez lent.
Comment devrais-je procéder pour remplacer les formules par du code VBA pour accélérer les opérations?

Merci pour tout tuyau.

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,
Teste ce code, mais sans garantie 😛

Application.ScreenUpdating = False
Dim I As Integer, J As Integer, Plage As Range
Sheets("Service1").Select
Set Plage = Range(Cells(1, 1), Range("A1").End(xlToRight))
J = Plage.Count
Plage.Copy
Sheets("Fiche individuelle 6 périodes").Range("S3").PasteSpecial _
    Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
Application.CutCopyMode = False
With Plage
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .Sort Key1:=Range("S3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With
Sheets("Switch").Range("K1").FormulaR1C1 = "Service1"
Sheets("Fiche individuelle 6 périodes").Select
Application.SheetsInNewWorkbook = I
Workbooks.Add
Application.SheetsInNewWorkbook = 3
ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\Administrateur\Bureau\Heures 2009\Récolte des fiches" _
        & "\Service1\Fiches individuelles Service1.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
Windows("Base.xls").Activate
For I = 1 To J
Range("P3").FormulaR1C1 = "=R[" & (I - 1) & "]C[3]"
Range("B1:P66").Copy
Windows("Fiches individuelles Service1.xls").Activate
With Sheets("Feuil" & I).Range("A1")
    .PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    .PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End With
Sheets("Feuil" & I).PageSetup.PrintArea = "$A$1:$O$66"
Sheets("Feuil" & I).Name = Range("O3").Value
Application.ExecuteExcel4Macro "PAGE.SETUP(,,0.7,0.2,0.2,0.2,,,TRUE,TRUE,1,9,true ,,,,,0.2,0.2,FALSE,FALSE)"
Next I
Windows("Base.xls").Activate
Sheets("Fiche individuelle 6 périodes").Select
Range("S3:S20").Clear
Application.ScreenUpdating = True

Bonne soirée 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Bonjour "JNP",

Merci pour ce code.

Seulement, il trie les noms dans la feuille d'origine ("Service2") au lieu de la feuille "Fiche individuelle"

Puis, cela ne fonctionne pas avec la création d'un nouveau classeur et l'ajoute d'autant de feuilles qu'il y a de noms.

Application.SheetsInNewWorkbook = I
Workbooks.Add
Application.SheetsInNewWorkbook = 3

Je ne comprends pas le sens du "Application..." avant le Workbooks.Add;
et puis ne nouveau classeur ne doit pas contenir 3 feuilles mais autant qu'il y a de noms.

Cependant, tout cela n'était en réalité pas mon "problème"!
Mon problème est le fait que le recalcul de toutes les formules à chaque fois qu'il y a un autre nom dans P3 est trop lent! Tout le reste fonctionne, même si c'est à ma manière!
A+

Aloha

 

[Staple1600]

Re : Remplacer des formules par VBA

Bonjour

Aloha
Quand tu ne comprends plus
Quand tu es perdu
Quand tu penses que tout est fichu
Non, ne va pas chez Prisu
Ouvres ce tableur si connu
Frappe simplement ces 3 touches:
ALT+F11 puis F1

Et tu tomberas dans un océan d'informations relatives à VBA.

 

[Aloha]

Re : Remplacer des formules par VBA

Bonjour,

Je sais, je sais, mais comment se retrouver dans un océan?
De tout on trouve là-dedans!
C'est cela le problème;
Tout de même!

Aloha

 

[Staple1600]

Re : Remplacer des formules par VBA

Re


Le code que je t'ai indiqué était adapté de l'enregistreur de macros
(J'avais fait Outils/Options/Général/ et j'ai changer le nombre de feuille par défaut

Quand à Application, qu'as-tu trouvé dans l'aide à ce sujet ?

Ceci, je suppose

Application, propriété


Renvoie un objet Application qui représente l'application dans laquelle l'objet spécifié a été créé (application conteneur). Vous pouvez utiliser cette propriété en conjonction avec un objet Automation pour renvoyer l'application conteneur. Propriété Object en lecture seule.

Ensuite quand je ne sais pas , j'appuie sur F2 dans VBE (pour afficher l'explorateur d'objet)
je saisis Application et je clique sur les jumelles

et quand je trouve des exemples de code issues de l'aide , je les teste, j'essaie de les modifier
Voila comment j'ai fait au début pour naviguer dans cet océan.

Ensuite , je suis venu ici car j'ai pas trouvé de Prisu 😉

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,
Comme je te l'ai dit, on navigue dans le noir... Sans ton fichier de départ (sans données confidentielles), il est fort possible que la plage flexible soit prise dans une mauvaise feuille, vu qu'on ne sait même pas dans quelle feuille on est...
Je peux te garantir que mon code fonctionnerait, avec les bonnes références de feuilles...
Mais bon, c'est toi qui "choice"...
Bonne soirée 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Bonsoir "JNP",
Bonsoir "Staple",
Bonsoir le forum,

Je ne peux malheureusement pas poster mon vrai classeur, anonymisé, car il est beaucoup trop gros pour joindre ici.

Je me permets de reprendre ma dernière question: comment faire pour remplacer ces formules par du code VBA?
Quel est p.ex. l'équivalent de SOMMEPROD() et de RECHERCHEV()?
A+

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,
Toutes les correspondances sur ce fil.
Bonne journée 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Bonjour,

Je ne comprends pas. Que veux-tu dire par là?
Le sujet auquel tu renvoies concerne "Tutorial commande VBA français-anglais"!

Bonne journée

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,
Dedans, j'ai joint le fichier VBALIST.XLS qui contient toutes les correspondances entre les formules en français, et leur traduction anglaise avec WorkSheetFunction.
Bon courage 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Re,

Merci pour le courage, mais en réalité je n'en ai pas besoin, puisque somme toute, mon système fonctionne; il est juste un peu lent.

CE qui m'intéresserait tout de même dans ce contexte, c'est d'apprendre comment remplacer des formules par du code.

A+

Aloha

 

[Aloha]

Re : Remplacer des formules par VBA

Coucou,

Il n'y a plus personne?

La mise en page de mes feuilles est extrêmement lente: l'ordi calcule tout le temps.
Peut-on enlever le calcul automatique pendant la mise en page, et le remettre pour la mise à jour des formules?

Ou alors, j'y reviens, comment procéder par VBA pour remplacer ces formules onéreuses SOMMEPROD()?

Bonne journée

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,
Le problème est que tu ne regardes pas (ou ne te poses pas de question) sur les réponses founies...

Application.ScreenUpdating = False

en début de code (mon post du 14 à 21h03) avec le même en False à la fin du code désactive/réactive l'affichage pendant l'exécution, ça va déjà accélérer ton code.
Le calcul automatique peux être arrêté/redémaré avec

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

mais attention à ce que tu n'ai pas besoin d'un calcul intermédiaire dans ta macro...
Dans tous les codes que je t'ai fourni, j'ai supprimé les Select, pour remplir les cellules afin d'aller plus vite

Sheets("Service1").Select
    Range("B1:R1").Select
    Selection.Copy
    Sheets("Fiche individuelle 6 périodes").Select
    Range("S3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

remplacé par

Sheets("Service1").Range("B1:R1").Copy
Sheets("Fiche individuelle 6 périodes").Range("S3").PasteSpecial _
    Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

va aussi aller beaucoup plus vite.
Enfin, pour utiliser des formules dans VBA, il faudrait qu'on sache quelle formules tu utilises, hors sans fichier... Mais je t'ai donné la fonction et le fichier de correspondance... donc SOMMEPROD est équivalent à

Application.WorksheetFunction.SumProduct

Bonne journée 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Bonjour,

Mais si, je regarde,...et je me pose également un certain nombre de questions.

P.ex. celle de savoir si empêcher l'ordinateur de rafraîchir l'écran fait gagner beaucoup en rapidité. Qu'en penses-tu?

Le code que tu m'as donné bloque à la ligne

Application.SheetsInNewWorkbpook = I

Je me demande s'il n'y a pas un problème avec cette variable I . Elle a été définie comme Integer, mais à la ligne citée elle n'a pas reçu de valeur, me semble-t-il.

..si je peux me permettre, car je ne comprends pas tout dans le code.

Bonne journée

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,
C'est Stapple qui l'a donné 😛...
Et VBA n'accepte pas les fautes d'orthographe

Application.SheetsInNewWorkbook = I
et non
Application.SheetsInNewWorkb[SIZE=4][COLOR=red][B]p[/B][/COLOR][/SIZE]ook = I

Un petit truc pour vérifier, tu tappes tout en minuscule, si l'instruction est bonne, les majuscules se mettent tout seul après le retour chariot, si elle ne se mettent pas, c'est que tu as un blème...
Pour le rafraîchissement écran, suivant le code, tu peux gagner jusqu'à 90% de rapidité...
Bonne journée 😎