VBA comment inscrire cette formule dans une série de feuilles (avec condition)

[fb62840]

Bonjour à toutes et tous,

Jusqu'à présent j'utilise ce type de rédaction afin d'inscrire une valeur dans une cellule et une formule dans une autre mais ça ne fonctionne pas.

If f.Name = "CLUB <8 R1" Then
        f.Range("E1") = "<8"
        f.Range("D2").Formula = "=SUMPRODUCT((Championnat R1!RC[7]:R[65534]C[7]='CLUB <8'!RC[-3])*(Championnat R1!RC[6]:R[65534]C[6]='CLUB <8'!R1C5)*(Championnat R1!RC[4]:R[65534]C[4]))"
End If

Avec ce type de rédaction j'aurai besoin de répéter un code similaire pour toutes les feuilles dans lesquelles je veux inscrire en "E1" le texte de la portion "centrale" du nom de la feuille "<8" ou "<10" ou "<12" ou "<15" ou "<18" ou "<50" ou ">=50"
et en D2 la formule de sommeprod dont j'ai besoin

Je suppose qu'il est possible de faire bien mieux en recherchant dans le nom de la feuille si le nom commence par "Club" puis en recherchant la partie "centrale" du nom de la feuille et enfin la partie "finale" du nom de la feuille

afin d'obtenir que toutes ces feuilles :
CLUB <8 R1, CLUB <8 R2, CLUB <8 R3, CLUB <8 R4, CLUB <8 R5
CLUB <10 R1 .../... CLUB <10 R5
CLUB <12 R1 .../... CLUB <12 R5
CLUB <15 R1 .../... CLUB <15 R5
CLUB <18 R1 .../... CLUB <18 R5
CLUB <50 R1 .../... CLUB <50 R5
CLUB >=50 R1 .../... CLUB >=50 R5

et pour deux feuilles nommées un peu différemment :
CLUB FEM R1, CLUB FEM R2, CLUB FEM R3, CLUB FEM R4, CLUB FEM R5

Merci pour vos conseils.

 

[Robert]

Re : VBA comment inscrire cette formule dans une série de feuilles (avec condition)

Bonjour Fb, bonjour le forum,

Peut-être comme ça :

Sub Ecrire1()
Dim f As Worksheet
'Boucler sur toutes les feuilles du classeurs
For Each f In ThisWorkbook.Sheets
    If Left(f.Name, 4) = "CLUB" Then
        f.Range("E1") = Split(f.Name, " ")(1)
        f.Range("G2").Formula = "=SUMPRODUCT((Championnat!RC[7]:R[65534]C[7]='" & f.Name & "'!RC[-3])*(Championnat!RC[6]:R[65534]C[6]='CLUB <8'!R1C5)*(Championnat!RC[4]:R[65534]C[4]))"
    End If
Next f
End Sub

 

[fb62840]

Re : VBA comment inscrire cette formule dans une série de feuilles (avec condition)

Bonjour Robert,

Merci de nouveau, pour cette réponse rapide.

A l'exécution du code j'ai une erreur "Erreur de compilation : instruction incorrecte à l'intérieure d'une procédure" sur la ligne

For Each f In ThisWorkbook.Sheets

 

[Robert]

Re : VBA comment inscrire cette formule dans une série de feuilles (avec condition)

Bonjour Fb, bonjour le forum,

Pour tester ton code je l'ai déplacé dans un module standard. Il manquait un Next f à la fin. Mais sinon le code que je t'ai envoyer marche chez moi dans un module standard... Essai peut-être avec :

Dim f As Object

 

[fb62840]

Re : VBA comment inscrire cette formule dans une série de feuilles (avec condition)

Re-bonjour Robert,

Oui effectivement mais ta solution ne convient pas parfaitement car la formule à recopier est "variable"
on fera référence aux données de la feuille Championnat R1 ou Championnat R2, ou ... Championnat R5
selon que l'on est sur une feuille nommée CLUB <8 R1, ou CLUB <10 R1 etc jusque CLUB >=50 R1
ou que l'on est sur une feuille "CLUB" dont le dernier terme est R2 ou R3 ou R4 ou R5
exemple CLUB <15 R3 dans laquelle la formule devrait être

"=SUMPRODUCT((Championnat R3!RC[7]:R[65534]C[7]='CLUB <15'!RC[-3])*(Championnat R3!RC[6]:R[65534]C[6]='CLUB <10'!R1C5)*(Championnat R1!RC[4]:R[65534]C[4]))"

Ma première explication était confuse je crains que celle-ci le soit aussi

 

[Robert]

Re : VBA comment inscrire cette formule dans une série de feuilles (avec condition)

Bonjour FB, bonjour le forum,

Je désespère, le code me paraît correct mais ça me demande l'ouverture d'un fichier que je n'ai pas et du coup la formule renvoie une erreur (peut-être à cause de ça mais je ne sais pas).
le code :

Sub Ecrire1()
Dim f As Worksheet
Dim ctr As String
Dim fin As String
Dim champ As String

'Boucler sur toutes les feuilles du classeurs
For Each f In ThisWorkbook.Sheets
    If Left(f.Name, 4) = "CLUB" Then
        ctr = Trim(Split(f.Name, " ")(1))
        fin = Trim(Split(f.Name, " ")(2))
        champ = "Championnat " & fin
        f.Range("E1") = ctr
        f.Range("G2").Formula = "=SUMPRODUCT((" & champ & "!RC[7]:R[65534]C[7]='" & f.Name & "'!RC[-3])*(" & champ & "!RC[6]:R[65534]C[6]='" & f.Name & "'!R1C5)*(Championnat R1!RC[4]:R[65534]C[4]))"
    End If
Next f
End Sub

 

[fb62840]

Re : VBA comment inscrire cette formule dans une série de feuilles (avec condition)

Bonjour Robert,

Merci pour tes efforts.
J'obtiens également la demande d'ouverture d'un fichier.

Je vais tenter de comprendre, mais j'avoue que ça me sera sans doute difficile.

Bonne journée

 

[fb62840]

Re : VBA comment inscrire cette formule dans une série de feuilles (avec condition)

Je crois avoir trouvé l'origine de "l'erreur".

En observant la formule résultant du code et la formule rédigée à la main il y avait des différences dans les colonnes de références qui n'étaient pas les bonnes et dans la façon dont était "nommée" la feuille Championnat dans la dernière portion du code (contrairement à la première portion elle ne faisait pas mention de la variable champ).

J'ai modifié ainsi et ça marche désormais... je m'étonne moi même, je l'avoue.

f.Range("G2").Formula = "=SUMPRODUCT((" & champ & "!RC[-3]:R[65534]C[-3]='" & f.Name & "'!RC[-6])*(" & champ & "!RC[+1]:R[65534]C[+1]='" & f.Name & "'!R1C5)*(" & champ & "!RC[4]:R[65534]C[4]))"

J'ai renommé les feuilles Championnat R1, Championnat R2...
Championnat_R1, Championnat_R2
et j'ai modifié dans le code cette partie :

champ = "Championnat_" & fin

au lieu de laisser "Championnat " & fin

Je crois que j'ai progressé un petit peu dans la rédaction des formules utilisant le type RC qui restait plutôt "flou" pour moi et qui a pris son sens ici.

Par contre il reste un problème important.
Sur les feuilles club il y a plusieurs lignes dans lesquelles devraient apparaître la formule ci-dessous, en fait, s'il y a 5 club inscrit sur cette feuille, il faudrait que chacune des cellules de la colonne G contienne la formule qui convient.
Une idée ?

merci encore.