VBA - Pb Insertion ligne avec copie formules

[oracle7]

Bonjour à tous,

Voici le problème pour lequel je sollicite SVP votre aide :

1 - Dans les cellules suivantes (A2 à B100) j'ai une formule du type (bien évidemment il y a en plus d'autres cellules utilisées sur chaque ligne) :

A2 --> =SI(A2=A1;FaireCalcul_1;FaireCalcul_2) B2 --> =A2+C2
A3 --> =SI(A3=A2;FaireCalcul_1;FaireCalcul_2) B3 --> =A3+C3
A4 --> =SI(A4=A3;FaireCalcul_1;FaireCalcul_2) B4 --> =A4+C4
A5 --> =SI(A5=A4;FaireCalcul_1;FaireCalcul_2) B5 --> =A5+C5
" "
" "
A100 --> =SI(A100=A99;FaireCalcul_1;FaireCalcul_2) B100 --> =A100+C100

2 - Pour insérer une ligne j'utilise la procédure suivante (appelée depuis un simple bouton) :

Sub InsèreCopieLigne()
  ActiveCell.EntireRow.Insert
  Rows(ActiveCell.Row + 1).Copy Rows(ActiveCell.Row)
  On Error Resume Next
  Rows(ActiveCell.Row).SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub

3 - Si maintenant j'insère par exemple une ligne entre les cellules A3 et A4, à l'aide de ma procédure : plus rien ne va !
En effet je constate qu'un décalage se crée dans les références, ce qui perturbe toute la logique mise en place initialement. Voilà les "dégats" :

A2 --> =SI(A2=A1;FaireCalcul_1;FaireCalcul_2) B2 --> =A2+C2
A3 --> =SI(A3=A2;FaireCalcul_1;FaireCalcul_2) B3 --> =A3+C3
A4 --> =SI(A4=A2;FaireCalcul_1;FaireCalcul_2) B4 --> =A4+C4 <-- Ligne insérée
A5 --> =SI(A5=A3;FaireCalcul_1;FaireCalcul_2) B5 --> =A5+C5 <-- ligne A4 avant insertion et qui a été décalée d'un rang vers le bas
A6 --> =SI(A6=A5;FaireCalcul_1;FaireCalcul_2) B6 --> =A6+C6

Au final dans la formule des cellules A4 et A5 la référence à la ligne précédente n'est pas correctement mise à jour que ce soit pour la ligne insérée comme pour la ligne décalée.
Alors que pour les cellules B4 et B5 tout est OK.

Y-a-t-il un truc pour palier à ce phénomène ?

J'espère avoir été suffisamment clair dans les explications du Pb rencontré.

D'avance MERCI pour m'aider à corriger ce qui ne va pas.
Cordialement
oracle7

 

[Efgé]

Re : VBA - Pb Insertion ligne avec copie formules

Bonsoir oracle7
Regardez si ce post peut t'être utile, Je pense qu'il ne faut pas copier la ligne du dessous mais la ligne active.
A toi de voir....
Cordialement

 

[job75]

Re : VBA - Pb Insertion ligne avec copie formules

Bonsoir oracle7, Efgé,

On peut toujours essayer ceci pour corriger les formules faisant référence à la colonne A :

Sub InsèreCopieLigne()
  ActiveCell.EntireRow.Insert
[COLOR="Red"]  Rows(ActiveCell.Row + 1).Replace "A" & ActiveCell.Row - 1, "A" & ActiveCell.Row, LookAt:=xlPart[/COLOR]
  Rows(ActiveCell.Row + 1).Copy Rows(ActiveCell.Row)
  On Error Resume Next
  Rows(ActiveCell.Row).SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub

Evidemment pas terrible s'il y a de nombreuses colonnes dans cette situation

A+

 

[job75]

Re : VBA - Pb Insertion ligne avec copie formules

Re,

Normalement il y a toujours les formules en ligne 2, alors :

Sub InsèreCopieLigne()
 [COLOR="Red"] If ActiveCell.Row < 3 Then Exit Sub[/COLOR]
  ActiveCell.EntireRow.Insert
  [COLOR="red"]Rows(2).Copy ActiveCell.EntireRow.Resize(2)[/COLOR]
  On Error Resume Next
  ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub

Voilà qui paraît mieux

Edit : oui, mais la cellule active ne doit pas être en ligne 2...

J'ai donc ajouté un test de sécurité.

A+

 

[oracle7]

Re : VBA - Pb Insertion ligne avec copie formules

Bonsoir à tous,

@ job75 et Efgé

Merci de vos réponses. J'en ai fait un "mixte" mais malheureusement sans arriver au but final.
L'astuce du resize(2) est intéressante et couplée au fait de copier la ligne de départ avant l'insertion avec resize marche bien mais lorsque je colle la ligne, là rien ne va plus.
De plus, par la suite si je supprime une ligne insérer avec cette méthode, je perds les références et récolte des #REF§ à tout va.
Donc j'abandonne donc mes formules Excel faisant référence à la ligne précédente pour une fonction VBA.

En tous cas, MERCI de votre aide bien sympathique sur ce sujet.

Cordialement
oracle7