Réfs de cellule mixtes : lig absolue (def par variable) et col relative
Bonjour,
J'ai un blocage dans mon code VBA, car je ne sais pas comment coder la référence à une cellule dont :
- la ligne est absolue (MAIS définie par une variable VBA, d'où la difficulté)
- la colonne est relative
Je précise que j'écris cette formule en tant que FormulaR1C1 dans mon code VBA.
On a envie de répondre, par exemple : R100C pour une ligne absolue 100 et une colonne relative qui suit la formule.
Mais ici, le numéro de ma ligne est définie par une variable (car ce numéro de ligne peut différer d'une feuille à l'autre).
J'ai alors envie d'écrire : Rnum_ligneC
mais ça ne marche pas -->erreur #NOM?
J'ai aussi essayé : cells(num_ligne, ligne(RC))
mais ça ne marche pas non plus ici, à cause de la "fonction" cells qui est une fonction VBA et non une fonction Excel je suppose...
-->erreur #NOM?
Avez-vous une astuce ?
Bonjour,
Il serait judicieux de joindre à minima un code pour illustrer tout ça...
Dans une formule R1C1 inscrite via VBA, la syntaxe est la suivante :
R100C1 <-- N° de ligne et colonne sont absolus (=fixes, correspondant à $A$100 ici)
R[-1]C1 <-- N° de ligne relative (ligne précédent celle dans laquelle la formule est placée) et N° colonne absolu (correspondant à $A100 ici, si formule placée en ligne 101)
Avec utilisation d'une variable VBA, ça donne quelque chose comme ça :
Range("Truc").FormulaR1C1 = "=MaFormule(R[" & MaVariable & "]C1)"Généralement, il est préférable d'évaluer directement la formule dans VBA, et d'inscrire son résultat "en dur" dans la cellule. La syntaxe est différente, du style :
Range("Truc") = Application.MaFormuleEnAnglais(Arg1,Arg2,...)ActiveCell.FormulaR1C1 = "=VLOOKUP(Rnum_ligne_fmC,plage_equivalent_mois_lettres_EB,2,FALSE)"Voici ma formule, il s'agit donc d'un RECHERCHEV
C'est une formule que je "fais glisser sur toute une ligne", et qui va chercher une cellule qui est située dans la même colonne, mais à une ligne plus haute, dont le numéro est déterminée par la variable num_ligne_fm .
Je me sers de cette variable pour déterminer le numéro de ligne car j'applique ma macro à différentes feuilles dont la 1re ligne de tableau est différente.
@Pedro22, ta proposition de faire R[" & MaVariable & "]C1 ne fonctionnerait pas puisque je définis un numéro de ligne absolu, pas un décalage par rapport à la ligne d'où j'écris ma formule.
Je cherche donc à écrire quelque chose comme RmavariableC, qui deviendrait après calcul R115C par exemple, si mavariable = 115.
@Pedro22 Quant à écrire directement la fonction avec Application, merci du conseil, effectivement c'est plus propre comme ça. Mais malheureusement ça ne suffit pas à résoudre pas mon problème j'ai l'impression, puisque qu'on retrouve toujours à écrire :
- soit une référence R1C1 qui ne fonctionne pas avec une variable à la place du numéro de ligne
- soit cells(mavariable, [même colonne]) mais ici je n'arrive pas à représenter [même colonne] dans cells().
@Pedro22, ta proposition de faire R[" & MaVariable & "]C1 ne fonctionnerait pas puisque je définis un numéro de ligne absolu, pas un décalage par rapport à la ligne d'où j'écris ma formule.
Justement, il suffit juste de retirer les crochets.
C'est parfaitement normal que ta formule actuelle ne fonctionne pas, car tu intègres ta variable dans le texte (délimité par des guillemets) correspondant à la formule. VBA écrit donc bêtement ce texte, tel que tu lui demande, sans interpréter le contenu de ta variable...
Je t'invite à faire un test très simple avec cette macro :
Sub UtiliserGuillemets()
num_ligne_fm = 1
MsgBox "=VLOOKUP(Rnum_ligne_fmC,plage_equivalent_mois_lettres_EB,2,FALSE)"
MsgBox "=VLOOKUP(R" & num_ligne_fm & "C,plage_equivalent_mois_lettres_EB,2,FALSE)"
End Sub@Pedro22 Quant à écrire directement la fonction avec Application, merci du conseil, effectivement c'est plus propre comme ça. Mais malheureusement ça ne suffit pas à résoudre pas mon problème j'ai l'impression, puisque qu'on retrouve toujours à écrire :
- soit une référence R1C1 qui ne fonctionne pas avec une variable à la place du numéro de ligne
- soit cells(mavariable, [même colonne]) mais ici je n'arrive pas à représenter [même colonne] dans cells().
Pour continuer, si les arguments de ta fonction sont des plages, il faut cette fois les écrire au format VBA... Donc pas de R1C1 ou autre. Voilà la syntaxe :
With Sheets("MaFeuille")
.Range("B1") = Application.VLookUp(.Range("A" & num_ligne_fm),Sheets("Feuil2").Range("A1:B100"),2,0) 'Les noms des plages (Range) et des feuilles (Sheets) sont à adapter
End WithA la place de Range("Truc"), tu peux utiliser Cells(NumLig,NumCol).
Pour une plage incluant plusieurs cellules : Range(Cells(1ereLigne,1ereColonne),Cells(DerLigne,DerColonne))
Bonjour @pedro22,
J'ai donc fait ton test et ça marche très bien !
C'est exactement ce que je cherchais. Heureusement que tu es là, parce que je crois que j'aurai jamais trouvé...surtout que mettre les guillemets avant les & est contre-intuitif par rapport aux formules Excel
"&variable&" vs "texte"&variable&"texte2" dans Excel.
Par contre, j'ai finalement gardé l'écriture type formule Excel (FormulaR1C1) parce que j'enregistre ma macro, je ne la code pas à la main, donc c'est plus simple pour moi. Aussi, "&variable&" ne fonctionne pas a priori dans les formules type VBA.
Merci beaucoup !
Aussi, "&variable&" ne fonctionne pas a priori dans les formules type VBA.
Ça fonctionne exactement pareil, je t'invite à relire attentivement la réponse précédente.
En utilisant une combinaison des syntaxes Range et Cells, tu peux utiliser sans soucis des variables. Si seule la ligne est variable, tu peux aussi écrire :
Range("A" & LigneVariable & ":C" & LigneVariable)Si ton problème initial est résolu, merci de cliquer sur le bouton correspondant...