Macro insertion de colonnes selon variable

[AILI06]

Bonjour,

je navigue sans succès sur le forum à la recherche d'une solution.

Aussi, je me décide à poster en espérant qu'une bonne âme (experte) puisse m'aider.

Voilà : cf fichier joint, je souhaite insérer un nombre de colonnes fixé par des variables : colonnes à partir de colonne D selon variable saisie en A2 et colonnes à partir de 0 selon variable saisie en B2. LA 1ère série de colonnes doit être une copie de la colonne D à l'exception de l'en tête qui doit être calée sur la variable. Exemple si QL = 5, les en-têtes doivent être QL1 en colonne D, puis QL 2 en colonne E....jusqu'à QL 5 en en-tête de colonne H. Même principe pour les colonnes créées selon la variable B2

Ces variables pouvant évoluer à la hausse ou à la baisse, il est important qu'en cas de suppression automatisée de colonnes, les formules contenues en N/Q et U ne soient pas flinguées.....

Merci par avance pour votre aide, je continue de chercher de mon côté.

FD

 

[AILI06]

Re : Macro insertion de colonnes selon variable

Je remonte dans la file vu le peu de succès de ma question.

Trop complexe ? ou passé inaperçu. de mon côté pas trop de succès dans mes recherches....

Merci !!

 

[Paf]

Re : Macro insertion de colonnes selon variable

bonjour,

quelques précisions nécessaires:
-ajouter/supprimer ou masquer/"démasquer" ferait l'affaire (plus facile à gérer)
- minimum et maximum du nombre de colonnes

Ces variables pouvant évoluer à la hausse ou à la baisse, il est important qu'en cas de suppression automatisée de colonnes, les formules contenues en N/Q et U ne soient pas flinguées.

en cas de suppression d'une colonne contenant des valeurs, le résultat des formules utilisant cette colonne en N/Q et U sera affecté.

A+

 

[AILI06]

Re : Macro insertion de colonnes selon variable

Merci PAF de t'être penché sur mon modeste pb.

Les formules des colonnes N et Q sont calées sur le nombre de cellules vides/non vides des colonnes précédentes. Du coup si on masque, je crains que les formules respectives intègrent des colonnes masquées ce qui fausserait le résultat .

En ce qui concerne les nb maxi c'est 20 colonnes maxi pour la partie QL (nbre de colonnes déterminé par la variable saisie en A2) et 10 maxi pour la partie VR (nbre de colonnes déterminé par la variable saisie en B2).

Merci,

FD

 

[Paf]

Re : Macro insertion de colonnes selon variable

re,

la macro pour la partie colonnes QL

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Byte, NbQl As Byte, j As Byte

If Not Intersect(Target, Range("A2")) Is Nothing Then
    If Range("A2").Value > 20 Then
        MsgBox "Nombre maxi(20) de colonnes dépassé"
        Exit Sub
    End If
    If Range("A2").Value = 0 Then
        MsgBox "Il doit y avoir au moins une colonne"
        Exit Sub
    End If
    
    For i = 4 To 24
        If Not Cells(2, i) Like "QL*" Then
            NbQl = i - 4 'détermination du nombre de col QL existant
            Exit For
        End If
    Next i
    
    If Range("A2").Value > NbQl Then
        For j = 4 + NbQl To 3 + Range("A2").Value
            Columns(j).Insert Shift:=xlToRight
            Cells(2, j) = "QL " & j - 3
            Range(Cells(1, 4), Cells(1, j)).MergeCells = True
        Next j
    Else
        For j = 3 + NbQl To 4 + Val(Range("A2").Value) Step -1
            Columns(j).Delete Shift:=xlToLeft
        Next j
    End If
End If

End Sub

il n'y a plus qu'à adapter pour la partie VR.
j'ai été un peu léger question commentaires, si besoin ....

Bonne suite

 

[AILI06]

Re : Macro insertion de colonnes selon variable

Merci PAF mais ça buggue.

Quelle que soit la valeur saisie en A2, j'ai une "erreur d'éxécution 6 , dépassement de capacité".

L'outil de débogage me place sur la ligne de code

For j=3 + Nb QL to 4 + val (range("A2").Value) Step -1

FD

 

[Paf]

Re : Macro insertion de colonnes selon variable

Bonjour,

Curieux ! ça eut marché mais ça marche plus ! du moins si on diminue le nombre de colonne.

modification:
remplacer :
Dim i As Byte, NbQl As Byte, j As Byte

Dim i As Byte, NbQl As Byte, j As Integer

c'est d'autant plus curieux que j ne dépasse jamais 255

Bonne suite

edit:je me repenche sur le Pb, a formule en colonne N n'est pas mise à jour! Jusqu'où faut il la copier/mettre à jour, à quoi correspondent les lignes grisées sans formule?

 

[AILI06]

Re : Macro insertion de colonnes selon variable

Super pour la partie QL PAF. L'Integer chasse tous les nuages.

Par contre j'ai essayé d'adapter les lignes de macro à la partie Vr comme tu le préconisais et là je me heurte à un nouvel écueil. L'emplacement de la 1ère colonne VR change de manière dynamique en fonction du nombre de colonnes QL créées dans l'étape 1.

Du coup je bloque.....

FD

 

[Paf]

Re : Macro insertion de colonnes selon variable

re,

Colonnes QL et VR avec mise à jour des formules colonnes "Besoin en Remplacement" et "Total VR dispo" en PJ

Pour la mise à jour, je me suis limité aux 30 premières lignes (soit Janvier)

bonne suite

 

[job75]

Re : Macro insertion de colonnes selon variable

Bonjour AILI06, Paf,

Voyez le fichier joint avec les listes de validation en A2 et B2 et ces macros :

Private Sub Worksheet_Change(ByVal Target As Range)
If [A2] = "" Or [B2] = "" Then Application.Undo
If Not Intersect(Target, [A2]) Is Nothing Then Insertion [A2], "QL"
If Not Intersect(Target, [B2]) Is Nothing Then Insertion [B2], "VR"
End Sub

Sub Insertion(c As Range, txt$)
Dim coldeb%, colfin%
'---1ère colonne---
coldeb = Application.Match(txt & 1, [2:2], 0)
'---dernière colonne---
colfin = Cells(2, Columns.Count).End(xlToLeft).Column
For colfin = colfin To coldeb Step -1
  If Cells(2, colfin) Like txt & "*" Then Exit For
Next
'---suppression ou insertion de colonnes---
If colfin - coldeb + 1 > c Then
  Columns(coldeb + c).Resize(, colfin - coldeb + 1 - c).Delete
ElseIf colfin - coldeb + 1 < c Then
  Columns(colfin + 1).Resize(, c - colfin + coldeb - 1).Insert
  Cells(2, coldeb).AutoFill Cells(2, coldeb).Resize(, c) 'remplissage
  Cells(1, coldeb).Resize(, c).Merge 'fusion
  Columns.AutoFit 'ajustement de la largeur
End If
End Sub

Nota 1 : j'ai supprimé l'espace après chaque "QL" puisqu'il n'y en a pas après les "VR".

Nota 2 : j'ai modifié les formules (avec DECALER) dans les 2 colonnes (ici N et Q).

Nota 3 : je ne me suis pas occupé des formules en lignes 34/35...

A+

 

[job75]

Re : Macro insertion de colonnes selon variable

Message annulé

 

[job75]

Re : Macro insertion de colonnes selon variable

Re,

Pour les formules en lignes 34/35 il faut ajouter ce code :

If c > 1 Then Cells(34, coldeb + 1).Resize(2, c - 1).UnMerge 'défusion
Cells(34, coldeb).Resize(2).AutoFill Cells(34, coldeb).Resize(2, c) 'remplissage

Mais il faudrait alors faire la même chose pour les autres mois...

Fichier (2).

A+

 

[job75]

Re : Macro insertion de colonnes selon variable

Re,

Bon voici tout le code s'il y a plusieurs "CA PLANIFIES" en colonne B :

Private Sub Worksheet_Change(ByVal Target As Range)
If [A2] = "" Or [B2] = "" Then Application.Undo
If Not Intersect(Target, [A2]) Is Nothing Then Insertion [A2], "QL"
If Not Intersect(Target, [B2]) Is Nothing Then Insertion [B2], "VR"
End Sub

Sub Insertion(c As Range, txt$)
Dim coldeb%, colfin%, n%, r As Range
'---1ère colonne---
coldeb = Application.Match(txt & 1, [2:2], 0)
'---dernière colonne---
colfin = Cells(2, Columns.Count).End(xlToLeft).Column
For colfin = colfin To coldeb Step -1
  If Cells(2, colfin) Like txt & "*" Then Exit For
Next
'---suppression ou insertion de colonnes---
If colfin - coldeb + 1 > c Then
  Columns(coldeb + c).Resize(, colfin - coldeb + 1 - c).Delete
ElseIf colfin - coldeb + 1 < c Then
  Columns(colfin + 1).Resize(, c - colfin + coldeb - 1).Insert
  Cells(2, coldeb).AutoFill Cells(2, coldeb).Resize(, c) 'remplissage
  n = Application.CountIf([B:B], "CA PLANIFIES")
  Set r = [B1]
  For n = 1 To n
    Set r = [B:B].Find("CA PLANIFIES", r, xlValues)
    Cells(r.Row, coldeb + 1).Resize(2, c - 1).UnMerge 'défusion
    Cells(r.Row, coldeb).Resize(2).AutoFill Cells(r.Row, coldeb).Resize(2, c)
  Next
  Cells(1, coldeb).Resize(, c).Merge 'fusion
End If
Columns.AutoFit 'ajustement de la largeur
End Sub

Fichier (3).

A+

 

[Paf]

Re : Macro insertion de colonnes selon variable

Re,
à job75
belle optimisation qui me permet de découvrir Resize et Autofill

 

[job75]

Re : Macro insertion de colonnes selon variable

Re,

Quelques améliorations :

- Application.Undo pouvait vous créer des soucis

- méthode Find pour trouver la dernière colonne

- méthode Copy dans la boucle n.

Private Sub Worksheet_Change(ByVal Target As Range)
If [A2] = "" Then [A2] = Application.CountIf([2:2], "QL*")
If [B2] = "" Then [B2] = Application.CountIf([2:2], "VR*")
If Not Intersect(Target, [A2]) Is Nothing Then Insertion [A2], "QL"
If Not Intersect(Target, [B2]) Is Nothing Then Insertion [B2], "VR"
End Sub

Sub Insertion(c As Range, txt$)
Dim coldeb%, colfin%, n%, r As Range
'---1ère colonne---
coldeb = Application.Match(txt & 1, [2:2], 0)
'---dernière colonne---
colfin = [2:2].Find(txt & "*", , xlValues, xlWhole, , xlPrevious).Column
'---suppression ou insertion de colonnes---
If colfin - coldeb + 1 > c Then
  Columns(coldeb + c).Resize(, colfin - coldeb + 1 - c).Delete
ElseIf colfin - coldeb + 1 < c Then
  Columns(colfin + 1).Resize(, c - colfin + coldeb - 1).Insert
  Cells(1, coldeb).Resize(, c).Merge 'fusion
  Cells(2, coldeb).AutoFill Cells(2, coldeb).Resize(, c) 'remplissage
  n = Application.CountIf([B:B], "CA PLANIFIES")
  Set r = [B1]
  For n = 1 To n
    Set r = [B:B].Find("CA PLANIFIES", r)
    Cells(r.Row, coldeb).Resize(2).Copy Cells(r.Row, coldeb + 1).Resize(2, c - 1)
  Next
End If
Columns.AutoFit 'ajustement de la largeur
End Sub

Fichier (4).

Edit : formule en C34 =SOMME(D34:Q34) - N34 et Q34 doivent rester vides.

A+