Problème suite à l'insertion de lignes dont certaines formules sont liées

mth

XLDnaute Barbatruc

• 28 Août 2011

• #2

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Bonjour,

Peut-être en utilisant la fonction INDIRECT(), par exemple pour la formule en A4:

Code:

="Exemple "&CTXT(DROITE(INDIRECT("A"&LIGNE()-1);1)+1;0)

en B4:

Code:

=1+INDIRECT("B"&LIGNE()-1)

E4:

Code:

=D4+INDIRECT("E"&LIGNE()-1)

F4:

Code:

INDIRECT("F"&LIGNE()+1)+1

Je te laisse tester pour voir si cela convient.

@ +

mth

 

Vote positif 0 Vote négatif

Dranreb

XLDnaute Barbatruc

• 28 Août 2011

• #3

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Bonjour
Vous illustrez tout l'intérêt de la fonction DECALER.
À tel point que je me suis mis cette procédure sur un bouton de barre d'outils !

VB:

Sub RemplaceRdifParDecaler()
Dim Rg As Range, ZSv As String, Z As String, dL As Long, P As Long, ZC As String, R As Long, Q As Long
For Each Rg In Selection
   ZSv = Rg.FormulaR1C1: Z = ZSv
   Do: P = InStr(Z, "R[")
      If P = 0 Then Exit Do
      Q = InStr(P + 2, Z, "]"): dL = Mid$(Z, P + 2, Q - P - 2)
      Q = Q + 1
      If Mid$(Z, Q, 1) = "C" Then
         ZC = Mid$(Z, Q)
         If Mid$(ZC, 2, 1) = "[" Then
            ZC = Left$(ZC, InStr(3, ZC, "]"))
         Else
            R = 1
            Do
               Q = R + 1
               If Q > Len(ZC) Then Exit Do
               If InStr("0123456789", Mid$(ZC, Q, 1)) = 0 Then Exit Do
               R = Q: Loop
            ZC = Left$(ZC, R)
            End If
      Else
         ZC = ""
         End If
      Z = Replace(Z, "R[" & dL & "]" & ZC, "OFFSET(R" & ZC & "," & dL & ",0)", , 1)
      Loop
   If ZSv <> Z Then Rg.FormulaR1C1 = Z
   Next Rg
End Sub

Elle transforme dans toutes les formules sélectionnées les références relatives à d'autres lignes en expressions DECALER par rapport à la ligne courante. Elle procède par transformation, dans les FormulaR1C1, de tous les R[dr]Cca en OFFSET(RCca,dr,0).
Elle ne touche donc pas aux référence absolues à d'autres lignes, bien sûr.

Cordialement.

 

Dernière édition: 28 Août 2011

Vote positif 0 Vote négatif

M

Michou9

XLDnaute Occasionnel

• 28 Août 2011

• #4

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Bonjour MTH

Effectivement cela semble fonctionner pour peu que toutes les formules soient ainsi modifiées.

Problème pour moi, c’est que mon tableau est très grand
Et qu’il va me falloir des heures pour modifier toutes mes formules.

A l’avenir je le saurai !
Mais il y aurait-il une autre possibilité dans le cas présent ?

Merci

A+

 

Vote positif 0 Vote négatif

Dranreb

XLDnaute Barbatruc

• 28 Août 2011

• #5

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Mais il y aurait-il une autre possibilité dans le cas présent

Cliquez pour agrandir...

Eh bien oui donc: Récupérez donc ma procédure dans un classeur de macros personnalisées, affectez la à un nouveau bouton de barre d'outils, elle fera le boulot sur toutes les cellules sélectionnées !
À+

 

Vote positif 0 Vote négatif

M

Monique

Nous a quitté

Repose en paix

• 28 Août 2011

• #6

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Bonjour,

Un essai avec la fonction Decaler()

A part modifier la 1ère ligne et la dernière cellule en bas à droite,
le reste n'est que du copié-collé

 

Pièces jointes

• InsertionLignes.xls

  34 KB · Affichages: 70
• InsertionLignes.xls

  34 KB · Affichages: 83
• InsertionLignes.xls

  34 KB · Affichages: 81

Vote positif 0 Vote négatif

Dranreb

XLDnaute Barbatruc

• 28 Août 2011

• #7

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Précision supplémentaire pour mon outil:
Les cellules qui, dans la sélection, ne contiennent aucune référence relative vers une autre ligne ne gènent en rien: elles ne subiront aucune transformation.
À+

 

Vote positif 0 Vote négatif

H

hoerwind

XLDnaute Barbatruc

• 28 Août 2011

• #8

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Bonjour, salut les autres,

Une autre approche, voir les formules sous le premier tableau du fichier joint.

 

Pièces jointes

• InsertionLignes.xls

  31.5 KB · Affichages: 60
• InsertionLignes.xls

  31.5 KB · Affichages: 62
• InsertionLignes.xls

  31.5 KB · Affichages: 64

Vote positif 0 Vote négatif

M

Michou9

XLDnaute Occasionnel

• 28 Août 2011

• #9

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Bonjour Dranreb

Ta macro pourrait fort bien m’intéresser

Mais je n’arrive pas à la mettre en œuvre

Pourrais-tu m’en dire un peu plus ?

Je l’ai implanté dans un module

Mais après ?

 

Vote positif 0 Vote négatif

Dranreb

XLDnaute Barbatruc

• 28 Août 2011

• #10

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Après ?
Clic droit sur une barre d'outils quelconque, Personnaliser, Chercher un bouton dans la rubrique Macro, glissez le jusqu'à une barre d'outils de votre choix, voire derrière le ? du menu Feuille de calcul (j'en ai plein là, moi)
Clic droit sur le bouton, Changer le titre, Affecter une macro, sélectionner la macro.
Il fera le traitement sur la sélection quand vous utiliserez le bouton.
À+

 

Vote positif 0 Vote négatif

M

Michou9

XLDnaute Occasionnel

• 28 Août 2011

• #11

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Re

J’ai saisi
L’action de ta macro, est de transposer les formules avec la fonction DECALER.
Ce qui me permet en suite de pouvoir faire des insertions de lignes.

Je ne connaissais pas cette fonction
Si je comprends bien cette fonction elle gère les autres cellules non pas leurs adresses mais par leur positionnement par rapport à la cellule qui contient cette fonction ???

Y a-t-il une différence avec la fonction INDIRECT proposée par MTH ?

Elle semble avoir le même effet ?

 

Vote positif 0 Vote négatif

Dranreb

XLDnaute Barbatruc

• 28 Août 2011

• #12

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Oui, c'est pareil, mais avec un inconvénient: la formule ne peut ni s'incrémenter horizontalement ni s'ajuster quand on déplace sa colonne car la lettre de colonne est figée en constante. Je réserve personnellement la fonction INDIRECT à des cas plus tordus, ou lorsque c'est la partie feuille, voire classeur, de la référence qui peut varier car puisée dans une cellule.
Cordialement.

 

Vote positif 0 Vote négatif

M

Michou9

XLDnaute Occasionnel

• 28 Août 2011

• #13

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Bonjour Monique

Merci pour ton fichier corrigé avec le détail de la fonction DECALER

Ton raisonnement « le reste n'est que du copié-collé » est juste
C’est d’ailleurs ce que je fais depuis longtemps
J’insère des lignes puis je corrige les formules devenues fausses en recopiant les formules issues des autres lignes qui elles n’ont pas été touchées par ces insertions.

Mon exemple n’était qu’un exemple très simpliste pour évoquer mon problème

Ma feuille de calcul est très longue, et les formules sont beaucoup plus complexes, mais surtout différentes suivant les zones
Je ne peux donc nullement utiliser un copier-coller général
Et cela me prend un temps colossal et implique des erreurs fatales en faisant des copier-coller au mauvais endroit

La macro de Dranreb pourrait donc m’être bien utile, car elle va me permette de modifier tous mes cellules concernées beaucoup plus rapidement.
J’espère qu’elle ne va générer d’incidence sur le reste de la formule qui n’est pas liées à une ligne ….

 

Vote positif 0 Vote négatif

M

Michou9

XLDnaute Occasionnel

• 28 Août 2011

• #14

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

Re Dranreb

Je viens de vérifier ta remarque au sujet des colonnes
Effectivement on ne peut pas déplacer les colonnes

Par contre je ne saisi pas bien ce dont tu parle avec l’incrémentation horizontale ?

 

Vote positif 0 Vote négatif

Dranreb

XLDnaute Barbatruc

• 28 Août 2011

• #15

Re : Problème suite à l'insertion de lignes dont certaines formules sont liées

J’espère qu’elle ne va générer d’incidence sur le reste de la formule qui n’est pas liées à une ligne

Cliquez pour agrandir...

Non. Pas d'inquiétude. La macro fait la chasse aux "R[" dans les FormulaR1C1. Elle ne réagit à rien d'autre, même pas aux références absolues à d'autres lignes. Si toutefois, par improbable, elle devait tomber sur un cas ou la formule corrigée serait à ramasser à la petite cuiller, ne manquez surtout pas de me le signaler, ça m'intéresserait beaucoup.

P.S.

l’incrémentation horizontale

Cliquez pour agrandir...

Pareil: si on traine la cellule par le petit carré de recopie incrémentée, la formule ne tient pas compte de la présense ou non d'un "$" à gauche de la lettre de colonne puisque c'est figé en constante, il ne fait pas d'incrémentation de la colonne.

Bonne soirée.

 

Dernière édition: 28 Août 2011

Vote positif 0 Vote négatif