Automatiser des formules avec VBA

Sylvain

XLDnaute Occasionnel

• 1 Avril 2009

• #2

Re : Automatiser des formules avec VBA

Bonjour,

en général les formules sont bien plus rapides que les macros en boucle.

Je propose :

Dim derligne
derligne = Range("b65536").End(xlUp).Row
Range("E2:E" & derligne).FormulaR1C1 = "=RC[-2]*COS(RC[-1]*PI()/180)"
Range("E2:E" & derligne).Value = Range("E2:E" & derligne).Value

 

Vote positif 0 Vote négatif

James007

XLDnaute Barbatruc

• 1 Avril 2009

• #3

Re : Automatiser des formules avec VBA

Bonjour,

Si tu veux construire ta première fonction personnalisée en VBA, je te conseille d'en choisir une ... de préférence la plus simple pour maitriser le processus ...
Et ensuite tu pourras sophistiquer les choses à souhait ...pour avoir la moyenne direction du vent ...

A +

 

Vote positif 0 Vote négatif

G

GCFRG

XLDnaute Occasionnel

• 1 Avril 2009

• #4

Re : Automatiser des formules avec VBA

Salut, a tester

Sub test()
Dim Ligne As Integer
With Sheets("Feuil1")
Dim X As Long 'abcisse de mon vecteur vitesse
For Ligne = 2 To Range("B" & Cells.Rows.Count).End(xlUp).Row
'X = Cells(ligne, 4) * Cos(Cells(ligne, 5) * Pi / 180)
.Range("e" & Ligne).FormulaR1C1 = "=r" & Ligne & "c3" & "* cos(" & "r" & Ligne & "c4" & "* pi()" & "/180)"
Next
End With
End Sub

 

Vote positif 0 Vote négatif

O

orianne40

Guest

• 1 Avril 2009

• #5

Re : Automatiser des formules avec VBA

Merci beaucoup!
ça m'a débloqué, je continue donc sur ma lancée!

Bonne aprem!

 

Vote positif 0 Vote négatif

O

orianne40

Guest

• 1 Avril 2009

• #6

Re : Automatiser des formules avec VBA

Re bonjour

J'essaye maintenant de calculer la moyenne de mes vitesses de vent en fonction de mes essais
j'ai d'abord tenter avec des If...then = average... end If

mais ça ne fonctionnait pas, du coup je me suis dirigée vers des sumproduct mais pas mieux :

With Sheets("Feuil1")
Dim lign As Integer
For lign = 2 To Range("J" & Cells.Rows.Count).End(xlUp).Row 'calcul ou J non vide
.Range("k" & lign).Value = Evaluate(" SumProduct(C:C * (B:B = cells(lign ,10)))" / "SumProduct((B:B = cells(lign, 10))" * 1)
Next
End With

il me marque incompatibilité de type (et je veux bien le croire...) ça doit etre car mes numéros d'essai sont écrit avec des lettres mais je ne vois pas comment faire...

 

Dernière modification par un modérateur: 1 Avril 2009

Vote positif 0 Vote négatif

James007

XLDnaute Barbatruc

• 1 Avril 2009

• #7

Re : Automatiser des formules avec VBA

Bonjour,

Pour bien utiliser la fonction evaluate() avec tes sumproduct() ... il te faut respecter la règle suivante :
Evaluate() ne fonctionne correctement qu'avec une série de strings qui doivent être la réplique parfaite de la formule équivalente contenue dans ta cellule ...
Donc, entre autres choses, il ne faut pas oublier que le premier signe est le signe = ... et ensuite il faut respecter la concaténation des strings avec &

Bon Courage

 

Vote positif 0 Vote négatif

O

orianne40

Guest

• 1 Avril 2009

• #8

Re : Automatiser des formules avec VBA

Merci !

j'ai fait attention de respecter la règle que vous m'avez spécifiée :

With Sheets("Feuil1")
Dim derlign As Integer
derligne = Range("B" & Cells.Rows.Count).End(xlUp).Row
Dim lign As Integer
For lign = 2 To Range("J" & Cells.Rows.Count).End(xlUp).Row
Range("k" & lign) = Evaluate("=SumProduct ((" & "C2:C" & derlign & " )*(" & "B2:B" & derlign & "=J" & lign & "))/SumProduct ((" & "B2:B" & derlign & "=J" & lign & ")*1)")
Next
End With

mais maintenant j'ai #Valeur! dans mes cellules excel, est ce une erreur de concaténation? ou y a-t-il une autre règle?

 

Vote positif 0 Vote négatif

James007

XLDnaute Barbatruc

• 1 Avril 2009

• #9

Re : Automatiser des formules avec VBA

Bonjour,

Il y a donc deux aspects ... le rédactionnel de l'instruction Evaluate()

Code:

Sub Test()
Dim derlign As Integer
Dim lign As Integer
derlign = Sheet1.Range("B" & Cells.Rows.Count).End(xlUp).Row
For lign = 2 To Sheet1.Range("J" & Cells.Rows.Count).End(xlUp).Row
    Sheet1.Range("k" & lign) = Evaluate("=SumProduct((C2:C" & derlign & " )*(" & "B2:B" & derlign & "=J" & lign & "))/SumProduct((B2:B" & derlign & "=J" & lign & ")*1)")
Next lign
End Sub

et la logique de la construction de la formule sumproduct() ... qui doit en elle même être logique et fonctionner dans une cellule ... avant d'être transposée en VBA ...

A +

 

Vote positif 0 Vote négatif

G

GCFRG

XLDnaute Occasionnel

• 1 Avril 2009

• #10

Re : Automatiser des formules avec VBA

Bonsoir, 1 P'tit conseil, met la 1ère lettre tes variables en Majuscules
dans la fomule tu ecris ta variable en minuscule, la 1ère lettre devra automatiquement passée en majuscule dans sinon c'est qu'il y a 1 erreur, il ne manquerais pas 1 .FormulaR1C1 ="
par hazard ??

@+ Gilbert😀

 

Vote positif 0 Vote négatif

O

orianne40

Guest

• 2 Avril 2009

• #11

Re : Automatiser des formules avec VBA

Bonjour à vous!

James: avant de transposer en vba je suis partie d'une formule qui fonctionnait :
=SOMMEPROD((C2:C45)*(B2:B45 =J2))/SOMMEPROD((B2:B45=J2)*1)

j'ai remplacé le 45 par ma variable derlign = dernière ligne de tableau
et le J2 par Jlign

j'ai repris tes modifications dans les lignes de code et le message est maintenant : "erreur 424 objet requis"

GCFRG : si je change ma première lettre de mes variables en majuscule, cela se fait automatiquement aux autres endroits...

y a t-il quelque chose à savoir si on rajoute un .FormulaR1C1=" ?

mon code en est là :

Sub Test()
Dim derlign As Integer
Dim lign As Integer
derlign = Sheet1.Range("B" & Cells.Rows.Count).End(xlUp).Row
For lign = 2 To Sheet1.Range("J" & Cells.Rows.Count).End(xlUp).Row
Sheet1.Range("k" & lign).FormulaR1C1 = "= Evaluate (" & "=SumProduct((C2:C" & derlign & " )*(" & "B2:B" & derlign & "=J" & lign & "))/SumProduct((B2:B" & derlign & "=J" & lign & ")*1))"
Next lign
End Sub

 

Vote positif 0 Vote négatif

pierrejean

XLDnaute Barbatruc

• 2 Avril 2009

• #12

Re : Automatiser des formules avec VBA

bonjour a tous

C'est soit
Evaluate pour avoir le resultat
Formula pour avoir la formule
mais on ne peut pas cumuler les deux
nb: preferer formula a FormulaR1C1 (voir aide) ou même pour les gaulois Formulalocal (dans ce cas on ecrit la formule telle qu'on l'ecrirait dans la cellule)

 

Vote positif 0 Vote négatif

O

orianne40

Guest

• 2 Avril 2009

• #13

Re : Automatiser des formules avec VBA

merci pierre jean, je vais rectifier ça desuite et voir ce que ça me donne

 

Vote positif 0 Vote négatif

O

orianne40

Guest

• 2 Avril 2009

• #14

Re : Automatiser des formules avec VBA

ha, ça y est, ça marche!
merci à tous!

Sub Test()
With Sheets("Feuil1")
Dim derlign As Integer
Dim lign As Integer
derlign = Range("B" & Cells.Rows.Count).End(xlUp).Row
For lign = 2 To Range("J" & Cells.Rows.Count).End(xlUp).Row
.Range("k" & lign) = Evaluate("=Sumproduct((C2:C" & derlign & " )*(" & "B2:B" & derlign & "=J" & lign & "))/Sumproduct((B2:B" & derlign & "=J" & lign & ")*1)")
Next lign
End With
End Sub

 

Vote positif 0 Vote négatif

O

orianne40

Guest

• 2 Avril 2009

• #15

Re : Automatiser des formules avec VBA

Bon ben je reviens demander quelques explications :
j'ai donc grâce à vous un code qui marche pour calculer ma moyenne des vitesses,
et je me suis dit qu'il fallait reprendre les mêmes lignes pour calculer ma direction moyenne en changeant juste la formule dans Evaluate() pour passer de :

=SOMMEPROD((C2:C45)*(B2:B45 =J2))/SOMMEPROD((B2:B45=J2)*1)
à
=180*ATAN2(SOMME.SI(B2:B60;J2; E2:E60);SOMME.SI(B2:B60;J2; F2:F60))/PI()

ce qui me donne en vba:

Sub Teste()
With Sheets("Feuil1")
Dim Derlign As Integer
Dim Lign As Integer
Derlign = Range("B" & Cells.Rows.Count).End(xlUp).Row
For Lign = 2 To Range("J" & Cells.Rows.Count).End(xlUp).Row
.Range("L" & Lign) = Evaluate("=180 * Atan2(SumIf(B2:B" & Derlign & ", J " & Lign & ", E2:E" & Derlign & "), SumIf(B2:B" & Derlign & ",J" & Lign & ",F2:F" & Derlign & ")) / pi()")
Next Lign
End With
End Sub

mais la j'ai beau mettre des Excel.WorksheetFunction.Atan2 à la place de Atan2, mettre Formulalocal au lieu de Evaluate, je n'y arrive pas...

 

Vote positif 0 Vote négatif