Discussion :

[Dédé6621]

Bonjour à toutes et à tous,

J'ai un gros problème: je dois insérer des formules dans différents classeurs dont les lignes sont variables et dois donc utiliser Cells dans Range.

Cependant, le code renvoie une erreur avec Cells:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Sub Test()
 
        Dim der As Integer
 
        der = 128
 
        Range(Cells(128, 3)).Formula = "=SUM(C6:C" & der - 1 & ")"
 
End Sub

Alors que si j'utilise le code suivant, avec la référence directement, ça marche:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Sub Test2()
 
        Dim der As Integer
 
        der = 128
 
        Range("C128").Formula = "=SUM(C6:C" & der - 1 & ")"
 
End Sub

Mais je ne peux alors plus utiliser de variable dans Range.

Quelqu'un-e pourrait-il/elle m'aider?

PS: même problème avec .FormulaLocal

[Philippe Tulliez]

Bonjour,
Dans ton exemple la ligne 7 de ton premier code ne contient pas non plus de variable pour le référencement.
Rien ne t'empêche non plus d'utiliser directement Cells au lieu de Range

Exemples de l'utilisation d'une variable dans un référencement de cellule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("A" & DerCel).Formula = "...."

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Cells(DerCel, 1).Formula="......"

etc....
(Ecris de mémoire donc non testé)

[Dédé6621]

Merci beaucoup Philippe!!! Ça marche

Ça contredit les recherches que j'avais faites jusqu'à présent. Merci encore. J'ai appris quelque chose.

PS:

Dans ton exemple la ligne 7 de ton premier code ne contient pas non plus de variable pour le référencement.

Oui, désolé, j'aurais pu être plus explicite et mettre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Sub Test()
 
        Dim der As Integer
 
        der = 128
 
        Range(Cells(der, 3)).Formula = "=SUM(C6:C" & der - 1 & ")"
 
End Sub

[Slooby]

Oui, désolé, j'aurais pu être plus explicite et mettre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Sub Test()
 
        Dim der As Integer
 
        der = 128
 
        Range(Cells(der, 3)).Formula = "=SUM(C6:C" & der - 1 & ")"
 
End Sub

NON c'est pas plus explicite !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range(Cells(der,3))

est un pléonasme informatique !!

Par exemple, pour désigner la cellule C2 tu as plusieurs choix:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
'Choix 1
Range("C2").
 
'Choix 2
Cells(2,3).

Range() et Cells() renvoient tous les 2 une range, mais avec des paramètres différents.
Donc il ne faut pas les combiner ensemble.

Slooby

[Philippe Tulliez]

Bonjour Slooby,

Range() et Cells() renvoient tous les 2 une range, mais avec des paramètres différents.
Donc il ne faut pas les combiner ensemble.

Excepté lorsque l'on référence une plage de cellules
Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 Dim rng As Range
 With ThisWorkbook.Worksheets("Feuil1")
  Set rng = .Range(.Cells(2, 1), .Cells(10, 5)) ' Plage A2:E10
 End With
 rng.Interior.Color = vbYellow
 Set rng = Nothing

[Slooby]

AHAHA oui là tu as trouvé l'exception :-)

Mais c'est parce que la méthode Range() peut définir une plage multiple quand on lui passe en paramètre sa borne début (qui est une cellule) et sa borne de fin (qui est aussi une cellule).
Donc vu que Cells() renvoie une cellule ( a fortiori une Range), ça marche.

Mais merci de soulever ce point, ça vaut la peine de l'expliquer !

Slooby

[Dédé6621]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range(Cells(der,3))

est un pléonasme informatique !!

Merci Slooby, c'est la véritable solution à mon problème !!!

Lorsque Cells n'est pas imbriqué dans Range, le message d'erreur disparaît

Le cas soulevé par Philippe Tulliez n'est à mon sens pas tout à fait une exception puisque j'aurais pu m'en servir (j'ai inséré la formule dans la première cellule de la plage et l'ai ensuite copié dans le reste de la plage, comme un novice que je suis). Cependant, il n'y a alors pas de message d'erreur !!!

Ainsi,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub Test()
 
        Range("F128:H128").Formula = _
            "=SUM(F6:F127)"
 
End Sub

devient sans problème:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Test()
 
        Dim derL As Integer         'dernière ligne
        Dim derC As Integer         'dernière colonne
        Dim ws As Worksheet
 
        Set ws = ActiveWorkbook.ActiveSheet
 
        derL = 128
        derC = 12
 
        Range(Cells(derL, 6), Cells(derL, derC - 4)).Formula = _
            "=SUM(F6:F" & derL - 1 & ")"
 
End Sub

Malgré tout, le même message d'erreur s'affiche avec Range(Cells,Cells).Copy, et, en l'occurrence, j'en ai besoin. Le style A1 permet d'utiliser des variables (ce qui est déjà super! merci à Philippe Tulliez pour l'avoir fait remarqué), mais la variable représentant une colonne devrait alors être une lettre. Ce qui n'est pas pratique lorsqu'on a un nombre au départ.

Il est certainement possible de convertir les nombres en lettre de colonne, y compris jusqu'à XFD. Mais par chance, mercatog a partagé une solution plus simple:https://www.developpez.net/forums/showthread.php?t=829799

La discussion est donc à nouveau résolue en ce qui me concerne.

PS: on ne peut pas utiliser le style R1C1 avec Range (https://msdn.microsoft.com/en-us/lib...fice.14).aspx).

[Philippe Tulliez]

Bonjour,

Le style A1 permet d'utiliser des variables (ce qui est déjà super! merci à Philippe Tulliez pour l'avoir fait remarqué), mais la variable représentant une colonne devrait alors être une lettre. Ce qui n'est pas pratique lorsqu'on a un nombre au départ.

Pourquoi ne pas utiliser Cells avec le numéro de la colonne au lieu d'un Range avec lettre de la colonne

PS: on ne peut pas utiliser le style R1C1 avec Range

Ah bon ?

La lecture de ces deux billets t'aidera peut-être Ecrire une formule dans Excel à l'aide d'une procédure VBA et Comment écrire une formule plus complexe dans une cellule

[EDIT]
En complément des deux articles que je te suggère de lire, voici un exemple pour écrire en cellule D2 de la feuille nommée [Feuil1] du classeur où se trouve la procédure VBA, la formule SOMME(A2:A11)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub t()
 Const myFormula As String = "=SUM(<Address>)"
 With ThisWorkbook.Worksheets("Feuil1")
 .Range("D2").Formula = Replace(myFormula, "<Address>", .Range(.Cells(2, 1), .Cells(11, 1)).Address)
 End With
End Sub

Par défaut la propriété Address de l'objet Range renvoie une adresse absolue. Pour renvoyer une adresse relative, mixte ou complète voir ces arguments.

[EDIT2]

Malgré tout, le même message d'erreur s'affiche avec Range(Cells,Cells).Copy

Je n'ai vu nulle part ton code avec la méthode COPY

Exemple du bon fonctionnement de la méthode Copy avec Range et Cells

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Sub CopyRange()
 Dim shtSource As Worksheet, shtTarget As Worksheet
 With ThisWorkbook
  Set shtSource = .Worksheets("Feuil1")
  Set shtTarget = .Worksheets("Feuil2")
 End With
 With shtSource
 .Range(.Cells(2, 1), .Cells(11, 1)).Copy shtTarget.Range("A1")
 End With
End Sub

[Dédé6621]

Bonjour,

Pourquoi ne pas utiliser Cells avec le numéro de la colonne au lieu d'un Range avec lettre de la colonne

Eh oui, c'est ce que j'ai écrit, mais imbriqué dans Range pour avoir une plage de cellules. C'est d'ailleurs le sujet de cette discussion et l'origine du problème qui vient de la nécessité d'utiliser Cells pour avoir une variable (ligne ou colonne) alors que ça provoque un message d'erreur, tandis que Range sans Cells n'en provoque pas. Merci à Slooby pour avoir résolu le problème avec une solution qui marche également avec une variable colonne.

D'où également mon intérêt pour le style R1C1, qui ne peut pas être utilisé avec Range.

Ah bon ?

Eh bien oui, comme indiqué dans une note de la page dont j'ai donné le lien (qui ne fonctionne pas je le reconnais , voici le bon: https://msdn.microsoft.com/en-us/lib...ffice.14).aspx): "If you use a text argument for the range address, you must specify the address in A1-style notation (you cannot use R1C1-style notation)." (Pour les non-anglophones: "Si vous utilisez un argument texte pour l'adresse de range, vous devez spécifier une adresse en notation de style A1 (vous ne pouvez pas utiliser la notation de style R1C1)").

La lecture de ces deux billets t'aidera peut-être

Merci pour la lecture!
Je ne connaissais pas la propriété CurrentRegion qui me semble potentiellement très utile.
L'usage des balises est très astucieuse. Petite question: les symboles <> sont bien là pour faciliter la lecture (surtout pour les familiers du langage html) mais ne sont pas en soi nécessaires, si?

Par défaut la propriété Address de l'objet Range renvoie une adresse absolue. Pour renvoyer une adresse relative, mixte ou complète voir ces arguments.

Qu'entends-tu par "voir ces arguments"?

Je n'ai vu nulle part ton code avec la méthode COPY

Tu as raison, c'est une sorte de digression. Simplement, je me disais que le message d'erreur apparaissant avec Range(Cells,Cells).Copy, et que dans ce cas, utiliser une variable pour les colonnes pose le même problème, ça pouvait intéresser la discussion. À vrai dire, j'ai trouvé la solution de mercatog alors que j'avais déjà commencé la rédaction du message. Je me suis dit que ça pouvait être utile de la partager étant donné que le problème de base (utilisation d'une variable rendant nécessaire l'emploi de Cells) était le même dans les deux cas.

Cordialement

[Philippe Tulliez]

Bonjour,

D'où également mon intérêt pour le style R1C1, qui ne peut pas être utilisé avec Range.

Mon questionnement "Ah bon ?" vient du fait que tu affirmes quleque chose d'inexact.

Syntaxe avec style R1C1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

RANGE("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],Feuil1!R2C1:R18C9,2,FALSE)"

Syntaxe avec style A1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

RANGE("B2").Formula = "=VLOOKUP(A2,Feuil1!$A$2:$I$18,2,FALSE)"

L'usage des balises est très astucieuse. Petite question: les symboles <> sont bien là pour faciliter la lecture (surtout pour les familiers du langage html) mais ne sont pas en soi nécessaires, si?

Le choix des caractères (< >) entourant la balise a été choisie arbitrairement. Tu pourrais parfaitement utiliser les crochets "[ ]".

Qu'entends-tu par "voir ces arguments"?

La propriété Address de l'objet Range a des arguments permettant de choisir le référencement Relatif (Absolu étant la valeur par défaut). Il suffit d'appuyer sur F1 après avoir mis le curseur de la souris su propriété Address pour obtenir l'aide sur la syntaxe.