Insertion de ligne(s) avec copie de certaines valeurs et formules
Bonjour le forum,
En complément à ma question « Ajout de lignes dans un tableau par rapport à la valeur d’une colonne », je me suis aperçu que la macro « Private Sub Worksheet_Change(ByVal Target As Range) » de la Feuil2 n’était pas tout à fait efficiente dans son travail d’insertion que j’attendais.
En effet il faudrait finalement que lorsque l’insertion de la ou des nouvelles lignes (qui se fait très bien lorsque je change la valeur de la colonne « I ») que le contenu des colonnes (A à BA) de la dernière ligne (avant insertion) soit dupliqué (formules et texte) à l’exception des informations (quand il y en a) des groupes de colonnes (K à O), (Q à S), (W à X), la colonne (AE) et enfin les colonnes (AG à AH). Il faudrait également que la colonne « A » et « E » ne soient pas fusionnées afin que je puisse faire des filtres dessus.
Je ne sais pas si j’ai été clair. J’ai mis en rouge l’entête des colonnes dans lesquelles il faut que les cellules restent vides et en commentaire les deux colonnes qui ne doivent pas être fusionnées après l’insertion.
Ci-dessous la partie de la procédure déjà en place pour l'insertion des lignes :
Option Explicit
Dim I_adresse As String
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, d, t, i&, derln&, c&, nb&, val&
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'// ajout lignes selon nombre entré en colonne I
If Not Intersect(Target, Me.Columns("I")) Is Nothing And Target.Count = 1 Then
If Me.Cells(Target.Row, "A") <> Empty Then
If MsgBox("Attention la valeurs de la colonne I a été modifiée. Voulez-vous continuer ?", vbYesNo) = vbNo Then Exit Sub
Application.EnableEvents = False
For i = Target.MergeArea.Rows.Count + 1 To Target.Value
Target.MergeArea.Offset(1).EntireRow.Insert
With Target.Offset(1).Offset(, 1)
.Value = "N°" & i
.Resize(, 44).Borders(xlEdgeTop).LineStyle = xlDot
End With
For c = 0 To Target.Column - 1
nb = i - Target.Offset(, -c).MergeArea.Rows.Count
Range(Target.Offset(, -c), Target.Offset(, -c).Offset(nb)).MergeCells = True
Next c
Next i
Application.EnableEvents = True
End If
End If
'// ajustement nombre en colonne I selon nombre de lignes
If I_adresse <> Empty Then
If IsNumeric(Me.Range(I_adresse)) Then
Application.EnableEvents = False
Me.Range(I_adresse) = Me.Range(I_adresse).MergeArea.Rows.Count
Application.EnableEvents = True
End If
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Cordialement.
Bonjour,
Je n'ai regardé de près ni ton fichier, ni ta macro, mais l'intitulé de ton sujet m'amènes à penser que tu te compliques beaucoup la vie en passant par macro... Alors qu'un tableau structuré permet de reporter automatiquement les formules et la mise en forme lors de l'ajout de lignes.
Salut Pedro22,
Dans le principe tu as raison mais dans mon cas ça ne marche pas car il y a des informations qui doivent se répercuter (nom de la formations, dates, n° des candidats etc..) sur la ou les nouvelles lignes insérer. Avec un tableau comme tu dis il n'y aurait uniquement que les formules qui s'impacteraient mais cela dit ton intervention est pertinent et je te remercie d'avoir apporté une réponse à ma question.
Cordialement.
Bonjour le forum,
Sur ma question j'ai pu avancer. Lorsque je modifie la valeur de la colonne "I" les nouvelles lignes qui sont créées prennent bien les valeurs pour les colonnes dans lesquelles je souhaitais voir copiées les informations de la ligne supérieur. Pour cela j'ai modifié la macro
'// ajout lignes selon nombre entré en colonne I
If Not Intersect(Target, Me.Columns("I")) Is Nothing And Target.Count = 1 Then
If Me.Cells(Target.Row, "A") <> Empty Then
If MsgBox("Attention la valeurs de la colonne I a été modifiée. Voulez-vous continuer ?", vbYesNo) = vbNo Then Exit Sub
Application.EnableEvents = False
For i = Target.MergeArea.Rows.Count + 1 To Target.Value
Target.MergeArea.Offset(1).EntireRow.Insert
'Ajoute le nouveau numéro des places dans la colonne J
With Target.Offset(1).Offset(, 1)
.Value = "N°" & i
.Resize(, 44).Borders(xlEdgeTop).LineStyle = xlDot
End With
'Copie les informations de la colonne P
With Target.Offset(1).Offset(, 7)
.Value = Target.Offset(1).Offset(-1, 7)
End With
'Copie les informations des colonnes S à U
With Target.Offset(1).Offset(, 23)
.Value = Target.Offset(1).Offset(-1, 23)
End With
'Copie les informations des colonnes S à U
For val = 10 To 12
With Target.Offset(1).Offset(, val)
.Value = Target.Offset(1).Offset(-1, val)
End With
Next val
'Copie les informations de la colonne AF
With Target.Offset(1).Offset(, 23)
.Value = Target.Offset(1).Offset(-1, 23)
End With
'Copie les informations des colonnes AJ à AM
For val1 = 27 To 30
With Target.Offset(1).Offset(, val1)
.Value = Target.Offset(1).Offset(-1, val1)
.WrapText = False
End With
Next val1
'Fusionne les colonnes A à I
For c = 0 To Target.Column - 1
nb = i - Target.Offset(, -c).MergeArea.Rows.Count
Range(Target.Offset(, -c), Target.Offset(, -c).Offset(nb)).MergeCells = True
Next c
'Fusionne la colonne V
nb = i - Target.Offset(, 13).MergeArea.Rows.Count
Range(Target.Offset(, 13), Target.Offset(, 13).Offset(nb)).MergeCells = True
'Next c
Next i
'Enlève la fusion des colonnes A et E
détail_de_la_colonne_A_et_E
Application.EnableEvents = True
End If
End If
Maintenant il faudrait qu'on me dise COMMENT je peux copier les formules des colonnes Z à AD . J'ai essayé la ligne de codes
'Copie des formules des colonnes Z à AD
For val2 = 17 To 21
With Target.Offset(1).Offset(, val2)
.Value = Target.Offset(1).Offset(-1, val2)
.WrapText = False
End With
Next val2
mais cela me donne que la valeur et non pas la formule de la ligne supérieur ce qui est logique puisque j'utilise "value =".
Existe-t-il un code pour les formules que je pourrais mettre à la place de "value =" ?
En PJ je mets le nouveau fichier avec les modifications apportées à la macro « Private Sub Worksheet_Change(ByVal Target As Range) » de la Feuil2.
Cordialement
As-tu essayé une instruction du style :
Range(A1).Formula = Range("A2").Formula
Bonjour,
Je n'ai pas trouvé où se trouvait le programme pour copier les formules, car tu as beaucoup de programmes, et que tu n'as pas précisé où le trouver, pour copier les formules il faut passer par formula, et non par value qui est la valeur, résultat du calcul de la formule, essaye de remplacer par ça :
For val2 = 17 To 21
With Target.Offset(1).Offset(, val2)
.FormulaR1C1 = Target.Offset(1).Offset(-1, val2).FormulaR1C1
.WrapText = False
End With
Next val2
Sinon quand je cherchais ton programme j'ai remarqué quelque chose d'horrible...
Sub detaildetouteslescolonnes()
Call DetaillerLaColonneA
Call DetaillerLaColonneB
Call DetaillerLaColonneC
Call DetaillerLaColonneD
Call DetaillerLaColonneE
Call DetaillerLaColonneF
Call DetaillerLaColonneG
Call DetaillerLaColonneH
Call DetaillerLaColonneI
Call DetaillerLaColonneV
End Sub
Tu as fait 10 programmes qui font exactement la même chose avec simplement un paramètre qui change...
Pourquoi ne pas traiter toutes tes colonnes en un programme? Ou à la limite faire une boucle où tu changes juste la lettre de la colonne avec une variable colonne?
Il y peut-être d'autres choses à revoir dans ton fichier mais ça c'est à toi de te demander si ce que tu as fait est vraiment bien, mais si tu retrouves le même programme plusieurs fois, c'est que tu peux forcément faire mieux, si tu as juste un paramètre qui change, passe le en variable ou trouve un moyen de regrouper tes programmes
Pour Ausecour :
Super ton code
Au sujet de :
Sub detaildetouteslescolonnes()
Call DetaillerLaColonneA
Call DetaillerLaColonneB
Call DetaillerLaColonneC
Call DetaillerLaColonneD
Call DetaillerLaColonneE
Call DetaillerLaColonneF
Call DetaillerLaColonneG
Call DetaillerLaColonneH
Call DetaillerLaColonneI
Call DetaillerLaColonneV
End Sub
J'ai fait cette macro car je ne savais pas comment m'y prendre autrement pour enlever la fusion de toutes les colonnes donc je suis preneur pour une solution plus efficiente.
Pour Pedro22 :
J'avais pensé à ta solution :
Range(A1).Formula = Range("A2").Formula
mais je ne savais pas comment l'intégrer dans ma macro.
Merci à vous deux pour avoir pris le temps de me répondre et de m'avoir trouver des solutions. Je mets le sujet en résolu.
Cordialement.
Tout d'abord, merci d'avoir passé le sujet en résolu
Pour ta question, quand je veux enlever la fusion de toutes les cellules de 3 colonnes par exemple, je sélectionnes les 3 colonnes et clique sur fusionner les cellules, ça enlève tout. Essaye de faire la même chose avec la fonction unmerge?
Essayes range("a:v").unmerge
Merci pour ta réponse sur comment enlever le fusionnement des cellules mais cette solution ne peut pas s'appliquer à mon tableau car je souhaite que la 1ère ligne "défusionnée" soit recopiée pour la formation concernée afin de pouvoir faire des filtres dessus. Par exemple pour la colonne F qui est le lieu de la formation si j'enlève la fusion je me retrouve avec uniquement la 1ère ligne de chaque formation rempli. Si je veux faire un filtre sur PARIS je n'aurai que deux lignes or qu'en vérité il y a 6 lignes.
Les différentes macro
Call DetaillerLaColonneA
Call DetaillerLaColonneB
Call DetaillerLaColonneC
Call DetaillerLaColonneDand
etc font ce travail.
Cordialement.