FormulaR1C1

Résolu/Fermé

Lili - 9 oct. 2017 à 06:56
Lili - 11 oct. 2017 à 06:07

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.

10 réponses

Réponse 1 / 10

Lili
9 oct. 2017 à 17:58

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.

Réponse 2 / 10

Kalissi Messages postés 218 Date d'inscription jeudi 2 mai 2013 Statut Membre Dernière intervention 15 juillet 2019 20
9 oct. 2017 à 17:36

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

Réponse 3 / 10

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
9 oct. 2017 à 19:23

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

Réponse 4 / 10

Lili
10 oct. 2017 à 06:09

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.

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
10 oct. 2017 à 10:28

Bonjour,
La cellule contenant la formule n'est-elle pas au format "TEXTE"?

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 10

Lili
10 oct. 2017 à 18:44

Non la cellule n'est pas au format texte.

Réponse 6 / 10

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
10 oct. 2017 à 19:44

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

Réponse 7 / 10

Lili
10 oct. 2017 à 20:55

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

Réponse 8 / 10

Kalissi Messages postés 218 Date d'inscription jeudi 2 mai 2013 Statut Membre Dernière intervention 15 juillet 2019 20
10 oct. 2017 à 21:54

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

Réponse 9 / 10

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
11 oct. 2017 à 04:26

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

Réponse 10 / 10

Lili
11 oct. 2017 à 06:07

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.

Discussions similaires

variable dans FormulaR1C1 VBA Excel

comment insérer une variable dans VBA FormulaR1C1

Comme intégrer une variable numerique d dans FormulaR1C1?

[vba] Formula R1C1 avec variables

Value Vs Formula R1C1

Discussions similaires

Newsletters