Guillemets dans VBA pour adressage indirect dans cellule

Version imprimable

Voir 40 message(s) de cette discussion en une page

27/06/2016, 19h13
nono63

2 pièce(s) jointe(s)

Guillemets dans VBA pour adressage indirect dans cellule

Bonjour,

Je ne sais pas si mon titre est explicite alors je vais aller directement au problème ;)

Dans un module VBA je souhaite écrire dans des cellules des adressages indirects.

Exemple qui fonctionne:
y: ligne qui s'incrémente
G1 & G2: coordonnées de cellules qui contiennent respectivement des nombres qui seront repris dans l'adresse indirecte

Code:

Range("F" & y) = "=""Datas!$"" & $E$" & y & " & G1 & "":$"" & $E$" & y & "& G2"

Ce qui donne dans la feuille Excel ce résultat correct:
Formule de la cellule:

Code:

="Datas!$" & $E$58 & G1 & ":$" & $E$58& G2

Qui donne ce résultat: Pièce jointe 214089 pour des cellules G1 de valeur 8651 et G2 de 10084
Tout fonctionne à merveille (même si j'ai bien noté que des $ étaient en double :) ), la colonne renvoyée "AC" est le résultat correct (valeur de la cellule cible)

J'ai voulu faire pratiquement la même chose mais en mettant le nom de la feuille dans une variable
qui se nomme: Onglet_data
Et c'est là que ça se complique !!

Si j'écris:

Code:

Range("F" & y) = "=""" & Onglet_data & "!"" & ""$E$"" & y & G1 & "":$"" & ""E$"" & y & G2"

je me retrouve avec cette formule dans la cellule:

Code:

="Datas_DE01!" & "$E$" & y & G1 & ":$" & "E$" & y & G2

(Datas_DE01 est le nom de la feuille, pas de problème)
On constate que la variable "y" est présente !! (alors que précédemment c'était seulement sa valeur qui était concaténée à la chaine "$E$"
Forcément la cellule me renvoie un "#VALEUR!"

J'ai essayé une multitude de combinaisons qui se soldent par une erreur VBA du style :
Pièce jointe 214092

Je ne sais plus comment faire !!
Pouvez-vous m'aider !!!!

@+

Nono
27/06/2016, 20h11
Zekraoui_Jakani

Guillemets dans VBA pour adressage indirect dans cellule

Fais un debug.print sur tes variables pour voir leur contenu exact, et corriger le tir
27/06/2016, 21h24
nono63

re,

Il n'y a aucun problème avec les variables !!
C'est uniquement l'imbrication des guillemets...
27/06/2016, 21h59
parmi

Bonjour,

Peut-être essayer comme ceci ?

Code:

Range("F" & y) = "=" & Onglet_data & "!$E$" & y & G1 & ":$E$" & y & G2

Ceci dit, on ne sait pas trop ce que contiennent y, G1 et G2
27/06/2016, 23h07
nono63

1 pièce(s) jointe(s)

Bonsoir,

Pour info:

y est une variable que j'incrémente dans une boucle et qui représente le numéro de ligne (donc un entier)
G1 et G2 sont également des entiers qui représente la ligne de début et de fin d'une plage de données pour un graphique dynamique.

La formule ci-dessus ne renvoie pas d'erreur mais ne donne pas le résultat escompté car la formule finale dans la cellule n'a rien à voir avec l'exemple que j'ai donné plus haut.
J'ai bien détaillé le problème dans mon premier post, le résultat attendu dans la cellule est:

Formule de la cellule:

Code:

="Datas!$" & $E$58 & G1 & ":$" & $E$58& G2

On y retrouve des guillemets.
Dans cette exemple y avait la valeur 58 et a été collée à la chaine "$E$"
La formule est la même que celle-ci qui fonctionne bien:

Code:

Range("F" & y) = "=""Datas!$"" & $E$" & y & " & G1 & "":$"" & $E$" & y & "& G2"

Sauf que "Datas!$" qui est le nom d'une feuille a été remplacé par une variable...

Je joins un fichier d'exemple, ce sera plus parlant !!

Pièce jointe 214106
27/06/2016, 23h23
parmi

Je ne vois pas comment une cellule peut contenir une série de données (AC8651:AC10084)
Sans le signe "=", ça pourrait donner ceci si E58 contient "AC"

Code:

Range("F" & y) = Onglet_data & "!$" & Range("E58") & Range("G1") & ":$" & Range("E58") & Range("G2")

Modification pour tenir compte de la variable "y", mais toujours sans le signe "="

Code:

Range("F" & y) = Onglet_data & "!$" & Range("E" & y) & Range("G1") & ":$" & Range("E" & y) & Range("G2")
28/06/2016, 00h13
nono63

1 pièce(s) jointe(s)

Citation:

Envoyé par parmi

Je ne vois pas comment une cellule peut contenir une série de données (AC8651:AC10084)
Sans le signe "=", ça pourrait donner ceci si E58 contient "AC"

Code:

Range("F" & y) = Onglet_data & "!$" & Range("E58") & Range("G1") & ":$" & Range("E58") & Range("G2")

Avez-vous essayer le fichier joint de mon dernier post ?

La cellule ne contient pas une série de données, elle contient une adresse de plage (qui est reprise dans en tant que plage de données dans un graphique).

Mon but est d'obtenir le même résultat avec le nom de feuille en variable que la version avec nom de la feuille écrit en fixe dans le code VBA.

Mon fichier joint expose parfaitement le problème !!

Pièce jointe 214112
28/06/2016, 00h59
parmi

Dans ta cellule F15 de ton fichier, on retrouve ceci

Code:

Feuil1!$AC10:$AC20

Le dernier code que je t'ai fourni retourne exactement la même chose.
Qu'est-ce que je n'ai pas compris ? ou que cherches-tu de plus ?
28/06/2016, 18h41
nono63

Bonjour,

En fait ça fonctionne très bien !!
J'ai focalisé sur le signe = qui n'apparait pas dans ta proposition mais n'est finalement pas obligatoire pour l'adressage indirect futur.

Un grand merci à toi :D pour cette solution rapide, je suppose que le décalage horaire y est pour quelque chose :)

@+
28/06/2016, 19h38
parmi

Citation:

Envoyé par nono63

Bonjour,

Un grand merci à toi :D pour cette solution rapide, je suppose que le décalage horaire y est pour quelque chose :)

@+

Effectivement, dans certains cas le décalage peut aider... mais pas toujours :)
28/06/2016, 22h02
nono63

Bonsoir,

J'ai répondu un peu trop vite !!
La méthode sans insérer le signe = fonctionne effectivement mais du coup la cellule ne se met pas à jour en fonction du changement de valeur des cellules G1 & G2 !!!
Car l'utilité de faire insérer des formules "EXCEL" est la souplesse à l'utilisation.
Avec cette solution, je suis obligé de relancer une procédure VBA pour actualiser le contenues des cellules !
Ok c'est réalisable facilement mais cela enlève la souplesse de départ ...
Donc le problème est à présent d'insérer le symbole = :)

@+
28/06/2016, 22h47
parmi
On revient donc au problème mentionné au départ, c'est-à-dire qu'on ne peut pas mettre une plage dans une cellule.
La formule va générer une erreur.
A1 peut être égal à B1, mais pas à B1:B10

La solution serait peut-être de mettre ta formule, mais au format Texte
Ça devrait passer, je pense...
Code:

1 2 Range("F" & y).Numberformat = "@" Range("F" & y) = "=" & Onglet_data & "!$" & Range("E" & y) & Range("G1") & ":$" & Range("E" & y) & Range("G2")
28/06/2016, 22h55
nono63

Non cela ne fonctionne pas, le signe = apparaît en début de cellule et l'adressage indirect ne fonctionne pas .
Je ne comprends pas pkoi juste le fait de mettre le nom de la feuille en variable chamboule tout !! (mon premier post)
28/06/2016, 23h08
parmi

Quel genre de formule utilises-tu avec Indirect ?
28/06/2016, 23h19
nono63

la cellule résultante, qui doit donc renvoyer une plage (dynamique grâce à G1 et G2 qui changeront) sert à la création en VBA
de noms qui y font référence du style:

Code:

=INDIRECT(Feuil1!$F$109)

Ce nom de cellule est ensuite utilisé dans un graphique, ce qui permet de le rendre dynamique.
En fait mon code produit 15 graphiques, chaque graphique a des données d'x, d'y, de datas, et 4 seuils !!, le tout dynamique !!

J'espère que c'est clair :-)

Je vais essayer de me débrouiller en relançant le code pour rafraichir les cellules, mais je perds la visu du graph en cours, même en utilisant Application.ScreenUpdating = False.
Le tableau remonte ...
28/06/2016, 23h32
parmi

Je viens de faire un test avec une formule RechercheV

En F15, il y a inscrit Feuil1!$AC10:$AC20
J'ai mis quelques chiffres simples dans la plage en question

Ma formule en G15 est

Code:

=RECHERCHEV(3;INDIRECT(F15);1;FAUX)

et ça fonctionne bien

Attention toutefois si le nom de l'onglet contient un ou des espaces: ça prend alors des apostrophes...
28/06/2016, 23h59
nono63

Désolé, je ne comprends pas la réponse !!
Tu veux démontrer quoi ?

Pour info:
Quand je parle de noms de cellule je fais allusion à ce que l'on retrouve dans le gestionnaire de noms.
Par exemple, une fois que la plage est correctement crée dans la cellule, je crée un nom de cellule qui la prend pour référence avec ce code:

Code:

ActiveWorkbook.Names.Add Name:=namez, RefersToR1C1:="=INDIRECT(Onglet_Data!R" & y - 1 & "C6)"

namez étant une string qui contient le nom.

Peux-tu mettre ton exemple dans un fichier ?
29/06/2016, 01h56
parmi

Tu sembles avoir un problème avec la fonction Indirect, donc...

Dans le cas de Feuil1, il n'y a pas de problème... pas de guillemets
Feuil1!$AC10:$AC20

Dans le cas d'une feuille qui se nommerait "Ma Feuille" (qui contient un espace), ça prend des guillemets
'Ma Feuille'!$AC10:$AC20

Donc, quand tu crées la "formule", tu dois pense à ajouter les guillemets.
01/07/2016, 23h12
nono63

Bonsoir,

On s’égare un peu là :)
Je n'ai pas de problème avec la fonction indirect, j'ai du mal l’expliqué, désolé.
J'ai essayé à nouveau ton code qui fonctionne bien mais m'oblige à relancer la macro pour rafraîchir les cellules.
Je n'ai plus le tableau qui remonte, mais la solution n'est pas top visuellement car le rafraichissement via code (même en désactivant le calcul automatique le temps du traitement et le screenrefresh) est plus lent et donc moins souple que la mise à jour des cellules via la méthode "formule dans cellule".
Je rappelle que cette macro sert à mettre à jour une dizaine de graphiques dynamiquement, donc c'est assez lourd.

Donc j'en reviens au premier problème: y a-t-il possibilité d'insérer cette plage sous la forme première "=" ?

@+
01/07/2016, 23h44
parmi

Oui, tu peux.
Tu n'as qu'à ajouter le signe égal comme dans le post plus haut.
Par contre, tu devrais voir un #Valeur ou autre type d'erreur dans cette ou ces cellules...

Peut-être aussi qu'il faudrait repenser le tout ?
Est-ce que ton fichier contient des données confidentielles ?
Si oui, peut-être peux-tu joindre un fichier du même genre avec des données bidons (?)

Voir 40 message(s) de cette discussion en une page