FormulaR1C1 Résolu/Fermé

Question

Bonjour, 
Sans connaissance vba, j'ai besoin de faire une petite macro pour insérer une formule dans un grand nombre de cellules. Avec un peu de  recherche sur le net et l'enregistreur de macro j'ai pu réussir à construire cette macro.

Mais une partie ne fonctionne pas. Cela concerne cette partie. 

         ActiveCell.FormulaR1C1 = _        "=(INDIRECT(""'""&(R2C18)&""\""&(R2C15)&""\""&(R2C12)&""\""&(R2C9)&""\[""&R2C6&"".xlsm]""&(R2C3)&""'!R[" & Col & "]C[" & Lig & "]""))"

Et plus exactement cette partie 
R[" & Col & "]C[" & Lig & "]

Après exécution de la macro la formule dans la cellule donne ceci :

=(INDIRECT("'"&($R$2)&"\"&($O$2)&"\"&($L$2)&"\"&($I$2)&"\["&$F$2&".xlsm]"&($C$2)&"'!R[9]C[5]"))

Merci pour votre aide.

Kalissi · Answer

Bonjour,

Truc perso :

1.) Construit ta formule avec l'enregistreur de macro.
2.) Capture la formule dans le bloc-note.
3.) Traite la formule comme un chaine (string)
4.) Affecte la formule comme un chaine.

Sub Test()

Dim Col As Integer
    Dim Lig As Integer
    Dim Formule As String
    
    Col = 9
    Lig = 5

Formule = _
        "=(INDIRECT(""'""&(R2C18)&""\""&(R2C15)&""\""&(R2C12)&""\""&(R2C9)&""\[""&R2C6&"".xlsm]""&(R2C3)&""'!R[" & Col & "]C[" & Lig & "]""))"
    
    ActiveCell.Value = Formule

End Sub


Déjà, je vois un problème avec la formule ...

"=(INDIRECT(""'""&(R2C18)&...

"=(INDIRECT(" & "'" & "(R2C18)" & ...

Il faut t'assurer que chaque partie de la formule est bel et bien traduit
dans la chaine de caractère.

Si tu as de la difficulté à interprété la formule.
Construit la avec l'enregistreur de macro et colle le résultat ici.

K

Lili · Answer

Bonsoir Kalissi et merci pour votre réponse.

La ligne de la macro que je poste ci-dessus a été réalisé par l'enregistreur de marcro comme celle qui suit. Ce code fonctionne fonctionne très bien.
ActiveCell.FormulaR1C1 = _        "=(INDIRECT(""'""&(R2C18)&""\""&(R2C15)&""\""&(R2C12)&""\""&(R2C9)&""\[""&R2C6&"".xlsm]""&(R2C3)&""'!$H$5""))"

Ce que je souhaite faire c'est rendre variable la partie qui fait référence à la cellule.
!$H$5""

La seul partie qui est de moi est celle-ci et qui ne fonctionne pas.
R[" & Col & "]C[" & Lig & "]

Une idée ? Bonne soirée et encore merci.

Frenchie83 · Answer

Bonjour
Beaucoup de parenthèses inutiles (avant INDIRECT, les encadrants de chaque cellules désignées, et à la fin)

Petite remarque: vous affectez la variable Col à une ligne(R) et la variable Lig à une colonne(C). Est-ce bien ce que vous voulez faire ou bien est-ce erreur de saisie de votre part?

Le reste de la formule doit être correct si vous avez utilisez l'enregistreur de macro.

Voilà comment j'aurai écrit la formule: 
ActiveCell.FormulaR1C1 = "=INDIRECT(""'""&R2C18&""\""&R2C15&""\""&R2C12&""\""&R2C9&""\[""&R2C6&"".xlsm]""&R2C3&""'!R[" & Col & "]C[" & Lig & "]"")"
ou, en inversant les variables Col et Lig:
ActiveCell.FormulaR1C1 = "=INDIRECT(""'""&R2C18&""\""&R2C15&""\""&R2C12&""\""&R2C9&""\[""&R2C6&"".xlsm]""&R2C3&""'!R[" & Lig & "]C[" & Col & "]"")"
 A tester 
Cdlt

Lili · Answer

Bonjour Frenchie83, merci pour votre réponse.

En effet j'ai inversé Col et Lig.

En utilisant votre proposition
ActiveCell.FormulaR1C1 = "=INDIRECT(""'""&R2C18&""\""&R2C15&""\""&R2C12&""\""&R2C9&""\[""&R2C6&"".xlsm]""&R2C3&""'!R[" & Lig & "]C[" & Col & "]"")"

Voici le résultat dans la barre de la formule, pas récupération de donnée.
=INDIRECT("'"&$R$2&"\"&$O$2&"\"&$L$2&"\"&$I$2&"\["&$F$2&".xlsm]"&$C$2&"'!R[5]C[9]")

Une autre suggestion peut-être. bonne journée à tous.

Lili · Answer

Non la cellule n'est pas au format texte.

Frenchie83 · Answer

Essayez ceci 
   Lig = 5
    Col = "H"
    ActiveCell.FormulaR1C1 = "=INDIRECT(""'""&R2C18&""\""&R2C15&""\""&R2C12&""\""&R2C9&""\[""&R2C6&"".xlsm]""&R2C3&""'!""&""" & Col & Lig & """)"
Cdlt

Lili · Answer

De cette manière cela fonctionne, voici le résultat dans la barre de la formule.
Mais j'ai absolument besoin de rendre variable le H et 5

=INDIRECT("'"&$R$2&"\"&$O$2&"\"&$L$2&"\"&$I$2&"\["&$F$2&".xlsm]"&$C$2&"'!"&"H5")

Merci pour votre aide Frenchie83, bonne soirée

Kalissi · Answer

Bonjour,

Je sais que ma façon de travailler est peu orthodoxe mais enfin...
Je traite toujours les formules au format texte dans un cellule au format texte.

et je me suis fabriquer de petites fonctions qui font le travail demandé.

Pour la ligne, ça ne doit pas poser de problème, et pour la colonne s'il n'y en as pas trop...

Public Function LettreSuivante(ByVal pLettre As String) As String

Dim Chiffre As Integer
    Dim Lettre As String
    
    If (Len(pLettre) = 1) Then
        If (pLettre <> "Z") Then
            Chiffre = Asc(pLettre)
            Chiffre = (Chiffre + 1)
            Lettre = Chr(Chiffre)
        Else
            Lettre = "AA"
        End If
    Else
        '...
    End If
    
    LettreSuivante = Lettre

End Function


Et ça fonctionne très bien...

K

Frenchie83 · Answer

Bonjour
Avec des variables
    Lig = 5
    Col = 8
    Var = Cells(Lig, Col).Address
    ActiveCell.FormulaR1C1 = "=INDIRECT(""'""&R2C18&""\""&R2C15&""\""&R2C12&""\""&R2C9&""\[""&R2C6&"".xlsm]""&R2C3&""'!""" & "&""" & Var & """)"
Cdlt

Lili · Answer

Bonjour Frenchie83,je viens de tester ça fonctionne. Il me reste plus qu'à construire la boucle pour renseigner l'ensemble des cellules.
Un grand merci. 

Bonjour Kalissi merci d'avoir apporter une solution à mon problème. 


Bonne journée à vous tous.

FormulaR1C1

10 réponses

Discussions similaires