Automatiser des formules avec VBA

[orianne40]

Bonjour,

J'essaye d'automatiser mes fomules excel, le but étant que n'importe qui n'ai qu'à copier coller ses données et de cliquer sur un bouton pour que tout se calcul sans probleme.

Les données (dans l'exemple en feuill1) correspondent à des essais avec des vitesses et des direction de vent. Le nombre de données peut varier jusqu'à plus de 30000.
Le but est de visulaliser sur une feuille (dans l'exemple en feuill2) si un essai est valide ou non (en fonction du vent)
J'ai déjà un fichier avec mes formules mais je n'arrive pas à les retranscrire en VBA...

J'ai commencé par essayer de calculer mes X (abcisses de ma direction de vent) :

Dim ligne As Long
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)
Next ligne

'je range les résultats dans une autre colonne
Cells(ligne, 6).Value = X

mais ça ne marche pas, je ne sais pas trop quoi faire...

 

[Sylvain]

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

 

[James007]

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 +

 

[GCFRG]

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

 

[orianne40]

Re : Automatiser des formules avec VBA

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

Bonne aprem!

 

[orianne40]

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...

 

[James007]

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

 

[orianne40]

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?

 

[James007]

Re : Automatiser des formules avec VBA

Bonjour,

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

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 +

 

[GCFRG]

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😀

 

[orianne40]

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

 

[pierrejean]

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)

 

[orianne40]

Re : Automatiser des formules avec VBA

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

 

[orianne40]

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

 

[orianne40]

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...