Cellule bougée <---> Résultat qui suit....

[WEIDER]

Bonjour à toutes et tous,

Pouvez-vous répondre à ma question s’il vous plait, certainement très simple pour vous…

Tout est dit dans mon fichier joint.

Mille mercis à vous,

Amicalement,

WEIDER

 

[CISCO]

Bonjour

J'ai comme l'impression que tu as oublié quelques mots dans ton fichier :
Que signifie la phrase "comment assurer à coup sur (en modifiant la formule) que la cellule 'B6' en feuille B" ?
Que faut-il écrire après cette phrase "Cette donnée peut changer de cellule mais se trouve toujours en colonne 'B' mais" ? Mais quoi ?

@ plus

 

[DoubleZero]

Bonjour, WEIDER, CISCO 🙂, le Forum,

Pourquoi ne pas nommer la cellule c12 de "FeuilA" : "ici", puis appliquer la formule, en "FeuilB" :

=ici

Mais... Peut-être n'ai-je pas compris le besoin.

A bientôt 🙂

 

[job75]

Bonjour WEIDER, CISCO, DoubleZero,

Cela paraît être un faux problème.

"Bouger" la cellule 'Feuille A'!C12 cela peut se faire en insérant des lignes au-dessus, des colonnes à gauche ou en faisant du Couper-Coller.

Dans tous les cas la formule en 'Feuil B'!B6 suit...

A+

 

[WEIDER]

Bonjour à tous,

Excusez moi, je me suis mal exprimé je pense.... 🙁

Nouvelle tentative... 😀
Autrement dit, et de façon plus conviviale, je souhaite que le résultat de la cellule B6 en feuille B, colle aux basques de la cellule C12 en feuille A, que cette dernière en feuille A soit déplacée ou qu'il y ai l'incrémentation de lignes ou de colonnes.

Peut être comme dit Job75, c'est automatique et rien n'est à faire de plus🙄

En faite, j'ai sur une feuille A toutes une nomenclature de référence pièce, et sur les 50 onglets suivant des modes opératoire allant chercher des références précises dans cette nomenclature.

 

[job75]

Re,

Peut être comme dit Job75, c'est automatique et rien n'est à faire de plus🙄

Si vous avez des doutes c'est que vous n'avez rien testé...

A+

 

[WEIDER]

Bien entendu j'ai testé la chose.

Mais par exemple, faire un simple tri sur la colonne C en ordre A-Z, et ma cellule en feuille B part en vrille 😕

 

[job75]

Bonjour WEIDER, le forum,

Vous auriez pu dire dès le début que c'était le tri qui vous posait problème.

Dans un tri les références des cellules ne sont évidemment pas modifiées.

Mais on peut obtenir ce que vous voulez avec du VBA et ce n'est pas simple.

Placez cette macro dans le code de "Feuille A" (clic droit sur l'onglet et Visualiser le code) :

Private Sub Worksheet_Calculate()
Dim n&, i&, r As Range, x$, c As Range
n = 100 'nombre de cellules cibles, à adapter
Application.EnableEvents = False
On Error Resume Next
For i = 1 To n
  Set r = Nothing
  Set r = Evaluate("Cible" & i)
  If Not r Is Nothing Then
1   x = "µµµ": Set c = Nothing
    x = Evaluate(ThisWorkbook.Names("TexteCible" & i).RefersTo)
    Set c = r.Parent.Cells.Find(x, , xlValues, xlWhole)
    If c Is Nothing Then ThisWorkbook.Names.Add "TexteCible" & i, r.Value
    c.Name = "Cible" & i
    Set r = Evaluate("Cible" & i)
    If Not Application.IsText(r) Then
      Application.Goto r
      r = "TexteCible" & i
      MsgBox "'Cible" & i & "' doit être un texte..."
      GoTo 1
    End If
  End If
Next
Application.EnableEvents = True
End Sub

J'ai mis quelques explications dans le fichier joint.

Un tri ne peut que créer l'évènement Calculate, et encore s'il y a au moins une formule volatile dans la feuille, c'est pourquoi j'ai mis la formule =AUJOURDHUI() en A2.

Chaque cellule "cible" doit être nommée manuellement, la macro crée le nom "TexteCiblex" qui est utilisé pour la recherche avec la méthode Find.

Il peut y avoir plusieurs cellules cibles nommées, d'après mes tests avec 100 cellules la macro s'exécutera en 0,6 seconde mais c'est à vous de tester avec le vrai fichier.

A+

 

[WEIDER]

Bonjour job75,

Merci beaucoup pour ce travail, je vais voir et tester tout ceci.
Merci encore à vous.

Amicalement

WEIDER45.

 

[job75]

Re,

Cette nouvelle solution est nettement plus simple et plus rapide :

Private Sub Worksheet_Calculate()
Dim n&, i&, r As Range, x$, P As Range, ncol%, tablo, lig&, col%
n = 100 'nombre de cellules cibles, à adapter
Application.EnableEvents = False
On Error Resume Next
For i = 1 To n
  Set r = Nothing
  Set r = Evaluate("Cible" & i)
  If Not r Is Nothing Then
    x = "µµµ"
    x = CStr(Evaluate(ThisWorkbook.Names("TexteCible" & i).RefersTo))
    Set P = r.Parent.UsedRange
    ncol = P.Columns.Count
    tablo = P.Value2 'matrice, plus rapide, Value2 nécessaire si date
    For lig = 1 To P.Rows.Count
      For col = 1 To ncol
        If CStr(tablo(lig, col)) = x Then P(lig, col).Name = "Cible" & i: GoTo 1
    Next col, lig
2   ThisWorkbook.Names.Add "TexteCible" & i, r.Value2
    If IsError(Evaluate(ThisWorkbook.Names("TexteCible" & i).RefersTo)) Then r = "TexteCible" & i & " ?": GoTo 2
  End If
1 Next
Application.EnableEvents = True
End Sub

Pour 100 cellules cibles la durée des calculs passe chez moi à 0,25 seconde.

Surtout le contenu d'une cellule cible peut être un texte, un nombre, une date, une valeur logique.

Si la cellule est vide ou contient une valeur d'erreur un texte de remplacement est introduit.

Fichier (2).

A+

 

[job75]

Re,

Une dernière (?) solution bien meilleure.

Si on ne touche pas à une cellule cible (pas de modification, pas de tri), le test x <> CStr(r.Value2)
permet d'occulter ce qui suit, l'exécution est alors quasi immédiate (0,04 seconde/100 cellules).

C'est donc très utile pour ne pas perdre de temps quand il s'agit des modifications des autres cellules :

Private Sub Worksheet_Calculate()
Dim n&, i&, r As Range, x$, P As Range, ncol%, tablo, lig&, col%
n = 100 'nombre de cellules cibles, à adapter
Application.EnableEvents = False
On Error Resume Next
For i = 1 To n
  Set r = Nothing
  Set r = Evaluate("Cible" & i)
  If Not r Is Nothing Then
    x = "µµµ"
    x = CStr(Evaluate(ThisWorkbook.Names("TexteCible" & i).RefersTo))
    If x <> CStr(r.Value2) Then
      Set P = r.Parent.UsedRange
      ncol = P.Columns.Count
      tablo = P.Value2 'matrice, plus rapide, Value2 nécessaire si date
      For lig = 1 To P.Rows.Count
        For col = 1 To ncol
          If CStr(tablo(lig, col)) = x Then P(lig, col).Name = "Cible" & i: GoTo 1
      Next col, lig
2     ThisWorkbook.Names.Add "TexteCible" & i, r.Value2
      If IsError(Evaluate(ThisWorkbook.Names("TexteCible" & i).RefersTo)) Then r = "TexteCible" & i & " ?": GoTo 2
    End If
  End If
1 Next
Application.EnableEvents = True
End Sub

Fichier (3).

Edit : voyez aussi la petite Workbook_Open dans ThisWorkbook.

A+

 

[WEIDER]

Bonjour job75,

J'avais commencé à regarder hier ta première proposition. Effectivement, la cellule résultat colle aux basquettes de la cible, c'est exactement ce que je souhaite.

Je vais regarder ce soir tes autres propos mais plus particulièrement ton avant dernière, car mes cellules Cibles peuvent êtres modifiées ou triées......

Merci beaucoup.

 

[job75]

Bonjour WEIDER, le forum,

Je vais regarder ce soir tes autres propos mais plus particulièrement ton avant dernière, car mes cellules Cibles peuvent êtres modifiées ou triées......

Dans toutes mes solutions les cellules cibles peuvent être modifiées ou triées, c'est fait pour ça !

Croyez-moi, la dernière solution (3) est la meilleure, mais vous ne semblez pas avoir compris pourquoi.

Encore une chose : toutes mes solutions fonctionnent bien s'il n'y a pas sur la même feuille des cellules ayant la même valeur que la cellule cible (pas de doublon), je crois avoir compris que c'est toujours le cas.

Bonne journée.

 

[job75]

Re,

Eh non, ce n'était pas fini.

Au lieu de repérer les cellules cibles par leur texte on peut les repérer en les dotant d'un commentaire.

En effet les commentaires suivent les cellules lors d'un tri.

Bien entendu chaque commentaire est créé automatiquement par la macro :

Private Sub Worksheet_Calculate()
Dim n&, i&, r As Range, c As Range
n = 100 'nombre de cellules cibles, à adapter
Application.EnableEvents = False
On Error Resume Next
For i = 1 To n
  Set r = Nothing
  Set r = Evaluate("Cible" & i)
  If Not r Is Nothing Then
    If r.Comment.Text <> "Cible" & i Then
      Set c = r.Parent.Cells.Find("Cible" & i, , xlComments, xlWhole)
      If Not c Is Nothing Then c.Name = "Cible" & i: GoTo 1
      '---création du commentaire---
      r.ClearComments
      r.AddComment "Cible" & i
      r.Comment.Shape.TextFrame.AutoSize = True 'dimensionnement
      r.Comment.Shape.TextFrame.Characters.Font.Bold = True 'gras (facultatif)
    End If
  End If
1 Next
Set c = Cells.Find("", , xlFormulas, xlPart) 'RAZ de la boîte de dialogue Rechercher
Application.EnableEvents = True
End Sub

Pour 100 cellules cibles c'est un peu plus long :

- 0,31 seconde sans le test r.Comment.Text <> "Cible" & i

- 0,09 seconde avec le test.

Mais il n'y a plus de problème en cas de doublon de texte dans la feuille.

Edit : inconvénient mineur, si l'on supprime certains commentaires juste avant d'effectuer un tri (c'est du vice) les cellules concernées ne seront pas renommées.

Pour y remédier (en partie) on peut ajouter cette macro :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = 0 Then Calculate
End Sub

Fichier joint.

A+

 

[WEIDER]

Merci job75,

A titre d'exemple et afin d'avoir une idée de mon document, en voici un échantillon avec 3 onglets seulement.
---> 1 onglet avec toute ma nomenclature de pièces et deux autres onglets (ou sont mes formules).