Utilisation de constantes prédefinies dans une formule

[Tini3]

Bonsoir à tous,
Voici mon problème.
J'ai deux fichiers xls par région: "*Attendance.xlsx" et "*Seminar.xlsx".
J'ai un fichier "Total.xlsx" dans lequel je dois multiplier des valeurs issues de "*Attendance.xlsx" par des valeurs provenant de "*Seminar.xlsx".
Je dois utiliser des constantes prédefinies pour faire reference à ces deux classeurs comme ils ont des préfixes différents selon les régions auxquelles ils appartiennent.
Est ce que quelqu'un peut me dire pourquoi ce code ne marche pas s'il vous plait?

Sub Essai9()
With Application: .ScreenUpdating = False: .Calculation = xlAutomatic: .EnableEvents = False: End With
        ChDir ActiveWorkbook.path
        a = Dir("*Attendance.xlsx")
        b = Dir("*Seminar.xlsx")
        Dim currentWb As Workbook
        Set currentWb = ThisWorkbook
        Dim wb1 As Workbook
        Set wb1 = Workbooks.Open(Filename:=a)
        Dim wb2 As Workbook
        Set wb2 = Workbooks.Open(Filename:=b)
        Dim openWs1 As Worksheet
        Set openWs1 = wb1.Sheets(1)
        Dim openWs2 As Worksheet
        Set openWs2 = wb2.Sheets(1)
    'La partie qui ne marche pas
        With currentWb.Sheets(1)
        .Range("B2").FormulaR1C1 = _
        "='" & openWs1 & "'!R2C2*'" & openWs2 & "'!R2C1"
       End With

End Sub

Je sais qu'il y a un moyen plus facile si je voulais juste obtenir la valeur que je veux comme par ce code qui marche bien:

currentWb.Sheets(1).Range("B2").Value = openWs1.Range("B2").Value * openWs2.Range("A2").Value

Mais j'ai vraiment besoin que quand je clique sur la cellule, je peux voir la formule s'afficher sur la barre des formules mais pas seulement la valeur.

Merci d'avance pour toute aide que vous pourriez apporter.

 

[Theze]

Re : Utilisation de constantes prédefinies dans une formule

Bonjour,

La formule attend des noms de feuilles et pas de variables objet :

With currentWb.Sheets(1)

    .Range("B2").FormulaR1C1 = "=" & openWs1.Name & "!R2C2*" & openWs2.Name & "!R2C1"
    
End With

Hervé.

 

[Tini3]

Re : Utilisation de constantes prédefinies dans une formule

@Hervé
Merci beaucoup pour votre réponse et sa rapidité.
Je n'ai effectivement plus de message d'erreur, le macro marche mais je ne sais pas pourquoi ça me renvoie une valeur 0.
Je vous mets en pièce jointe les 3 fichiers d'essais. Je suis sur que d'un coup d’œil vous comprendriez ce qui ne va pas.
Merci d'avance

 

[Staple1600]

Re : Utilisation de constantes prédefinies dans une formule

Bonsoir à tous

Trois façons d'écrire cette multiplication "inter-classeurs)

Pour tester Ouvrir Excel et cliquer 3 fois sur nouveaux classeurs
Dans le classeur 1, copies la macro ci-dessous
En A1, du classeur 2, saisir un nombre
En A2 du classeur 3, saisir un nombre
Revenir dans le classeur 1 et lancer les macros
On a bien des formules fonctionnelles qui renvoient le bon résultat

PS: J'ai testé avec 2 en A1 (Classeur2) et 3 en A1 (Classeur2), et j'obtiens bien 6 dans le classeur 1.
Code VBA:
Sub Sommons()

Dim a As Workbook, b As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set a = Workbooks("Classeur2"): Set sh1 = a.Sheets(1)
Set b = Workbooks("Classeur3"): Set sh2 = b.Sheets(1)

With ThisWorkbook.Sheets(1)
.[A1].FormulaR1C1 = "=" & sh1.Parent.Name & "!R1C1*" & sh2.Parent.Name & "!R1C1"
.[B1].FormulaLocal = "=" & sh1.Parent.Name & "!A1*" & sh2.Parent.Name & "!A1"
.[C1].FormulaR1C1 = "=" & "PRODUCT(" & sh1.Parent.Name & "!R1C1," & sh2.Parent.Name & "!R1C1)"
End With
End Sub

 

[Tini3]

Re : Utilisation de constantes prédefinies dans une formule

Bonsoir à tous,

Merci Staple1600, les trois façons marchent bien avec votre exemple; je n'ai pas de message d'erreur avec la solution de Theze non plus d'ailleurs.

Mais je pense que le probleme avec mes fichiers c'est que je fais reference à des classeurs fermés dans mes formules. J'ai tout le temps une boite de dialogue "Update Values" qui me demande de spécifier le chemin de mes classeurs avant d'effectuer les calculs, ce qui est très ennuyant pour moi, car c'est justement pour éviter ça que j'utilise des constantes.

Si quelqu'un a une idée pour désactiver cette boite de dialogue "Update Values" et juste faire le calcul comme j'ai déjà specifié le chemin au debut du code, toute indication serait la bienvenue?

Merci d'avance.