pour que la valeur d'une cellule devienne la référence d'une autre cellule

david54520 · 20 Août 2015

bonjour à tous

Depuis plusieurs heures je bloque sur un truc.

j'ai braconné une macro et je l'ai adapté pour la création de fléche sur la feuille ("dpt") depuis un tableau sur feuille ("admin1")

voila mon code :
Sub test11()

Sheets("admin1").Range("e3:Ar12").Select

Dim c As Range
Dim celdep As String 'cellule début fleche classe 4
Dim celfin As String ' cellule fin fleche classe 4

For Each c In Selection
If c.Value Like "classe 4" Then

celdep = c.Offset(0, -2).Value
celfin = c.Offset(0, -1).Value
MsgBox celdep

Dim sh As Shape, Ligne1 As Long, Ligne2 As Long, Colonne1 As Long, Colonne2 As Long

Set sh = Sheets("dpt").Shapes.AddLine(Range(celdep).Left + Range(celdep).Width / 2, Range(celdep).Top + Range(celdep).Height / 2, Range(celfin).Left + Range(celfin).Width / 2, Range(celfin).Top + Range(celfin).Height / 2)

sh.Line.EndArrowheadStyle = msoArrowheadTriangle
sh.Line.EndArrowheadLength = msoArrowheadLengthMedium
sh.Line.EndArrowheadWidth = msoArrowheadWidthMedium
sh.Line.ForeColor.SchemeColor = 10
sh.Line.Weight = 3#

'Coordonnées flèche
Ligne1 = sh.TopLeftCell.Row
Colonne1 = sh.TopLeftCell.Column
Ligne2 = sh.BottomRightCell.Row
Colonne2 = sh.BottomRightCell.Column

End If
Next
End Sub

dit autrement :
-je cherche la valeur "classe4' dans une selection de la feuille admin1
- pour chaque cellule je me décale de 2 colonne puis de 1 pour avoir les valeur indiquées dans ces deux nouvelles cellules
- ces deux cellule devraient me donner un référence de cellule (celdep et celfin) que j'utilise en suite pour la création de fléches

MAIS

- en donnant une valeur à celdep et celfin du genre "d8" et "g13", la fléche se crée. le processus de création fonctionne apparemment bien
- si j'arrête la sub après le message box, je constate que toutes les cellules recherchées sont bien trouvées
- les valeurs dans les cellules ciblées sont de type 14,6 (j'ai essaye de concatener en vba pour avoir une "d8", mais le problème ci dessous reste entier
- lors d'un autre teste il y a quelques jours, toutes les flèches se sont bien crées sur la feuille dpt, mais toutes décalées de plusieurs colonnes.

bref, quand je lance le truc, il trouve bien la première cellule et m'affiche le msgbox mais à la ligne :
Set sh = Sheets("dpt").Shapes.AddLine(Range(celdep).Left + Range(celdep).Width / 2, Range(celdep).Top + Range(celdep).Height / 2, Range(celfin).Left + Range(celfin).Width / 2, Range(celfin).Top + Range(celfin).Height / 2)

çà bug
erreur 1004 : méthode range de l'objet global a échoué

j'ai l'impression que le problème tourne autour de la valeur de celdep et celfin qui n'est pas vue comme une référence de cellule.

Et je ne sais plus comment faire malgrés des recherches sur forum !!

une aide sera la bienvenue.
Merci d'avance !

david

Dranreb · 20 Août 2015

Re : pour que la valeur d'une cellule devienne la référence d'une autre cellule

Bonjour

Il faudrait qu'on puisse voir ce que contiennent réellement les 2 cellules à gauche de celles qui sont like "classe 4".
Cela dit, si ce sont bien des adresses de cellules (bien qu'il y ait de grandes chance qu'on puisse y mettre autre chose de moins technique), plutôt que de les noter dans des String et obliger Excel à reconstituer 4 fois les Range correspondants, notez les donc directement dans des Range comme ça :

VB:

Dim FAdmin As Worksheet, FDpt As  Worksheet, C As Range
Dim CelDep As Range ' Cellule début fleche classe 4
Dim CelFin As Range ' Cellule fin fleche classe 4
Dim Sh As Shape, Ligne1 As Long, Ligne2 As Long, Colonne1 As Long, Colonne2 As Long
Set FAdmin = Worksheets("admin1")
Set FDpt = Worksheets("dpt")
For Each C In FAdmin.[E3:AR12]
   If C.Value Like "classe 4" Then
      On Error Resume Next
      Set CelDep = FAdmin.Range(C.Offset(0, -2).Value): If Err Then Application.Goto C.Offset(0, -2): Exit Sub
      Set CelFin = FAdmin.Range(C.Offset(0, -1).Value): If Err Then Application.Goto C.Offset(0, -2): Exit Sub
      On Error GoTo 0
      MsgBox CelDep.Address(external:=True)
      Set Sh = FDpt.Shapes.AddLine(CelDep.Left + CelDep.Width / 2, CelDep.Top + CelDep.Height / 2, _
         CelFin.Left + CelFin.Width / 2, CelFin.Top + CelFin.Height / 2)

Ça aurait de plus l'avantage d'employer le méthode Range de l'objet Worksheet, qui est plus sure que celle de l'objet Application, et vous seriez fixé si une adresse de cellule est incorrecte

david54520 · 20 Août 2015

Re : pour que la valeur d'une cellule devienne la référence d'une autre cellule

merci Dranreb !
merci de vous intéresser au sujet et pour cette rapide réponse !

Je viens d'essayer votre code (où je ne comprend pas tout, je suis assez novice en VBA...) : je n'ai plus de message d'erreur, mais....pas de flèches de créer

pour répondre à votre interrogation sur le contenu des cellules cibles près de celle like classe 4 :

- elles contiennent une formule : recherchev

Globalement :
- le futur utilisateur entre une série de données (des chiffres) dans un tableau "résultats"
- toutes ces données sont ensuite extraites dans une colonne et triées
- l'utilisateur attribue ensuite une classe pour chaque chiffre (par exemple classe 4 pour les résultats de 0 à 10, classe 3 pour données entre 11 et 20, etc...). l'idée est ensuite de modéliser ces chiffres par une flèche de couleur sur une carte pour donner une information sur des flux entre départements
- cette carte est dispo sur la feuille DPT (une série de forme (celles des départements) agencées ensemble
- j'ai choisi deux des cellules qui se trouve derrière chaque forme (celdep et celfin) pour déterminer l'endroit de départ et/ou l'endroit d'arrivée des flèches pour chaque forme/département.
- sur la feuille admin1 (dans un tableau "coordonnées"), j'ai donc indique pour chaque département une cellule où se trouve le chiffre de la colonne de départ de celdep et une cellule où se trouve ligne de la cellule de départ. Même chose pour celfin
- enfin, dans mon tableau de recap (e3-ar12) on retrouve donc pour chaque département la celdep et la celfin via un recherchev sur le ttableau coordonnées. dans ce tableau de recap on trouve aussi la classe qui a été attribué et la valeur du flux enregistré au départ.

il faudra, quand j'aurais résolu ce problème de flèches, que je positionne aussi la valeur sur chaque fleche.

En espérant vous avoir éclairer pour la compréhension du problème

Bien cordialement, David

Dranreb · 20 Août 2015

Re : pour que la valeur d'une cellule devienne la référence d'une autre cellule

Ben des n° de lignes et de colonnes ce ne sont pas des adresses de cellules…
Écoutez je n'ai rien sous les yeux qui me permette de comprendre vos explications. Il me faudrait voir le fichier.
Aller en mode avancé, Gérer les pièces jointes.
Si aucune flèche n'est créée c'est qu'aucun C.Value n'est exactement = "classe 4" (un Like quelque chose qui ne contient aucun caractère générique revient à un = quelque chose).

david54520 · 20 Août 2015

Re : pour que la valeur d'une cellule devienne la référence d'une autre cellule

re

En complément de mon mail précédent :

voici un code similaire qui fonctionne sans erreur....ou presque

Sub test11()

Sheets("admin1").Range("e3:Ar12").Select

Dim cell As Range
Dim celdep As String 'cellule début fleche classe 4
Dim celfin As String ' cellule fin fleche classe 4

For Each cell In Selection
If cell.Value Like "classe 4" Then

celdep = cell.Offset(0, -2).Value

celfin = cell.Offset(0, -1).Value
MsgBox celdep

Dim sh As Shape, Ligne1 As Long, Ligne2 As Long, Colonne1 As Long, Colonne2 As Long

Set sh = Sheets("dpt").Shapes.AddLine(Range(celdep).Left + Range(celdep).Width / 2, Range(celdep).Top + Range(celdep).Height / 2, Range(celfin).Left + Range(celfin).Width / 2, Range(celfin).Top + Range(celfin).Height / 2)

sh.Line.EndArrowheadStyle = msoArrowheadTriangle
sh.Line.EndArrowheadLength = msoArrowheadLengthMedium
sh.Line.EndArrowheadWidth = msoArrowheadWidthMedium
sh.Line.ForeColor.SchemeColor = 10
sh.Line.Weight = 3#

'Coordonnées flèche
Ligne1 = sh.TopLeftCell.Row
Colonne1 = sh.TopLeftCell.Column
Ligne2 = sh.BottomRightCell.Row
Colonne2 = sh.BottomRightCell.Column

End If
Next
End Sub

sauf que les flèches ne sont pas créées au bon endroit. Par exemple, une flèche qui a théoriquement une celdep =cellule y16 et celfin v37, se retrouve finalement en bc17 pour le depart et bc34 pour l'arrivée, soit 30 colonne plus loin.
elles finissent toutes décalées de la sorte.
par contre, elles sont toutes dans le bon sens (départ / arrivée) et prennent toutes la bonne direction, mais pas au premier plan des formes des départements. Mytère

pour info dans cette sub ci dessus celdep et celfin sont en format type v37, en non pas numéro de colonne et de ligne comme la sub poste ce matin.

Par contre, elles n'ont pas de guillemets genre cells("v37")
merci pour le coup de main.

David

david54520 · 20 Août 2015

Re : pour que la valeur d'une cellule devienne la référence d'une autre cellule

Je n'ai pas vu votre message avant de poster mon précédent.
En m'inspirant des cours de J BOISGONTIER sur l'adressage, il me semblait avoir compris qu'il était possible de caractériser une cellule par son numéro de colonne et de ligne genre cell(3,1).
peut-être ai mal compris.
je vous envoi les fichiers

david54520 · 20 Août 2015

Re : pour que la valeur d'une cellule devienne la référence d'une autre cellule

comme demande
la v2 genere les fleches mais au bon endroit
la v3 bloque comme indiquee ce matin.

merci beaucoup!

david

Dranreb · 20 Août 2015

Re : pour que la valeur d'une cellule devienne la référence d'une autre cellule

elles finissent toutes décalées de la sorte.

Ce sont probablement, devant des expressions Range, de mauvaises expressions Worksheet précisées ou assumées faute de spécification fortement recommandée.

Ça me fait d'ailleurs penser que dans la solution que j'avais indiquée ce n'est peut être pas
Set CelDep = FAdmin.Range(C.Offset(0, -2).Value) qu'il faut prendre mais
Set CelDep = FDpt.Range(C.Offset(0, -2).Value)

david54520 · 20 Août 2015

Re : pour que la valeur d'une cellule devienne la référence d'une autre cellule

merci !!!!

En suivant vos commentaires, j'ai remis mes tableaux de données et intermédiaires sur la feuille dpt pour éviter de jongler avec les feuilles.

Et.....

çà fonctionne !! les fléches se positionnent au bon endroits !

il doit surement exister un moyen d'écrire proprement la sub initiale pour le passage entre les différentes feuilles, mais je ne suis pas assez doué en VBA et....après plusieurs jours de recherche, je vais m'arrêter sans doute à cette solution.
En tout cas pour le moment.

Merci encore !

Cordialement , david

Dranreb · 20 Août 2015

Re : pour que la valeur d'une cellule devienne la référence d'une autre cellule

À partir du moment où vous avez vos variables As Worksheet initialisées c'est très simple de "jongler" comme vous dites avec plusieurs feuilles sans avoir à les activer. Il suffit de vous interdire à tout jamais, comme moi je l"ai fait depuis très longtemps, d'utiliser les méthodes Range ou Cells sans préciser quelque chose devant suivi d'un point. De préférence un nom pris dans la rubrique Microsoft Excel Objets, suivi du nom rappelé entre parenthèses de la feuille Excel qu'il représente.

pour que la valeur d'une cellule devienne la référence d'une autre cellule

david54520

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

david54520

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

david54520

XLDnaute Junior

david54520

XLDnaute Junior

david54520

XLDnaute Junior

Pièces jointes

Dranreb

XLDnaute Barbatruc

david54520

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

Discussions similaires