Insertion de cellules VBA

@Marjorie

tu a écrit :

Mon fichier de base n'a qu'une seule colonne en plus au début

donc :

* ta colonne "Quantité" est en H et pas en G

* ta colonne "test" est en K et pas en J

* ta formule en K2 doit être : =-(H2<=-500)

dhany

Re,

Oui je suis nulle en VBA mais j'ai quand même pensé à modifier ma formule par rapport à mes colonnes

Le problème doit venir d'ailleurs, mais alors là impossible de savoir d'où ...

Marjow54

nouveau code VBA (avec 3 modifs) :

Option Explicit

Sub InsertLine()
  Dim n&: n = Cells(Rows.Count, 11).End(3).Row  '11 au lieu de 10 : colonne K au lieu de J
  If n = 1 Then Exit Sub
  Dim Tbl, i&: Tbl = [K1].Resize(n)  '[K1] au lieu de [J1]
  For i = n - 1 To 2 Step -1
    If Tbl(i, 1) = -1 Then Cells(i + 1, 6).Resize(, 6).Insert 2, 1  'Cells(i + 1, 6) au lieu de Cells(i + 1, 5)
                                                              'c'est donc la colonne F au lieu de la colonne E,
                                                              'mais à droite, c'est toujours .Resize(, 6)
  Next i
End Sub

c'est déjà fait dans ce fichier :

dhany

* dans mon post précédent, j'ai ajouté des commentaires dans le code VBA pour t'indiquer quelles sont les 3 modifs

* j'ai pas mis ces commentaires dans le code VBA du fichier ; il est inchangé

* dans ce fichier, j'ai ajouté une colonne au début ➯ toutes les autres ont été décalées d'une colonne à droite

* fais Ctrl e ➯ ça marche aussi bien qu'avant !

dhany

Merci, ça marche mais toujours pas totalement...

J'ai toujours un décallage de deux lignes au deuxième -1, 3 lignes au troisième etc.

Je pense avoir compris d'où ça vient. Comme il y a beaucoup de lignes et que ca tourne, surement que excel me décale une première fois au premier -1, mais du coup comme la colonne test se décale aussi, le deuxième -1 se décale d'une ligne. Donc au lieu par exemple d'avoir mon deuxième -1 à la ligne 362, il se retrouve à la 364 donc insère un ligne en 365 au lieu de 364.

Je ne sais pas si c'est clair.

Donc dans la formule il faudrait que je dise de ne pas insérer de cellule dans la colonne test, mais juste les colonnes d'avant.

Marjow54

Loupé, ca n'a pas marché

voici ce que fait la macro :

* elle lit les valeurs de la colonne K de bas en haut

* à chaque fois qu'elle rencontre un -1, elle décale d'une seule ligne vers le bas les colonnes F à K, à partir de la ligne en dessous

* 1er -1 rencontré en K362 ➯ en ligne 363, insertion de cellules pour les colonnes F à K, donc les valeurs de F363:K417 passent en F364:K418 ; colonnes A à E : aucun changement ➯ ce n'est plus aligné (mais c'était ta demande) ; pour éviter ça, ça serait peut-être mieux d'insérer une ligne complète ?

* 2ème -1 rencontré en K227 ➯ en ligne 228, insertion de cellules pour les colonnes F à K, donc les valeurs de F228:K418 passent en F229:K419 ; colonnes A à E : aucun changement ➯ c'est encore moins aligné qu'avant !

* note bien qu'y'a eu 2 décalages ➯ au total, y'a eu 2 lignes ajoutées

dhany

Pourquoi lui dire d'aller de bas en haut et pas de haut en bas ?

Comment lui dire cela ?

Je voulais t'ajouter un fichier pour te montrer mais ca me dit que le fichier est trop volumineux ...

En gros la quand je lance la macro au lieu d'avoir une insertion en 364, elle est en 365 ; au lieu de 537 elle est en 539 et ainsi de sute

il faut faire l'opération de bas en haut, car une ligne est ajoutée pour chaque -1 rencontré ; si on faisait de haut en bas, ça gênerait le compteur de boucle i de la sub.

* ci-dessous, un fichier exemple avec 8 lignes de données seulement ; en C6, j'ai mis 3000 ➯ en K5, il y a -1 ; y'en n'a pas d'autre : c'est le seul -1 de la colonne ➯ y'aura un seul décalage

* fais Ctrl edécalage bas d'une seule ligne des données de F6:K9, qui passent donc en F7:K10 ; et c'est vide en F6:K6

maint'nant, retourne-moi ce fichier avec un exemple du résultat attendu : ça sera plus clair qu'une description.

3exemple-court.xlsm (16.76 Ko)

dhany

Voila le résultat attendu en feuille 2 et ce que j'obtiens en feuille 3

Merci beaucoup de prendre du temps pour m'aider !!!

7exemple-court.xlsm (23.99 Ko)

Salut Marjow54,

Salut l'équipe,

il y a déjà tellement de cerveaux sur l'affaire que j'ai dû louper un truc...

Double-clic sur la feuille pour démarrer la macro..

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
Dim tData
'
Cancel = True
Application.ScreenUpdating = False
'
tData = Range("K1:K" & Range("B" & Rows.Count).End(xlUp).Row).Value
For x = UBound(tData, 1) To 2 Step -1
    If CInt(tData(x, 1)) = -1 Then Range("E" & x + 1 & ":K" & x + 1).Insert shift:=xlDown
Next
'
Application.ScreenUpdating = True
'
End Sub

A+

En regardant de plus près, j'ai exactement la même chose que Dhany et, forcément, le même résultat...

C'est quoi cette histoire de décalage ? Pige pas !

@Marjorie

4exemple-court.xlsm (28.20 Ko)

* d'après ton nouvel exemple, j'ai mis sur "Feuil1" ton tableau initial avant exécution de la macro

* va sur "Feuil2" : c'est exactement le même tableau ; fais Ctrl e ➯ on obtient la même chose

que ce qui sur ta feuille "Résultat attendu" ➯ ma macro est OK !

* aussi, j'vois pas c'que t'as pu faire pour aboutir à c'qui est sur ta feuille "Ce que j'obtiens"


Bonjour curulis57,

tu a écrit :

En regardant de plus près, j'ai exactement la même chose que Dhany et, forcément, le même résultat...

merci d'avoir confirmé mes résultats !

dhany

Juste, je ne sais absolument pas d'où vient le décalage.

J'ai essayé en fixant ma colonne test (donc avec les -1) (copier-coller des valeurs) mais ca ne marche pas non plus, il m'insére toujours en décalage c'est à dire qu'il met en 539 au lieu de 537 pour le troisième -1 rencontré par exemple.

Je ne comprends pas ...

@dhany; si tu copie plusieurs fois le tableau pour avoir un tableau avec des milliers de lignes tu verras que normalement ca ne marche pas (car je veux que les cellules en jaune soient sur la même ligne).

Salut Marjow54,

Salut l'équipe,

il ne te reste plus qu'à supprimer 109.000 lignes de ton fichier et de nous l'envoyer après anonymisation éventuelle avec une deuxième feuille corrigée manuellement sur quelques -1...

Un indice pour nous repérer : le fond des tableaux, très chaotique, que l'on obtient tous les 3 est-il ce que tu attends ?

A+

@Marjorie

j'ai trouvé où est ton problème !

va sur ta feuille "Ce que j'obtiens" ; fais Ctrl 3 (celui de la rangée haut du clavier, qui est avec le guillemet) ➯ ça montre les formules ; fais défiler la feuille à droite pour voir entièrement (en largeur) ta colonne K avec les formules ; tu dois alors voir ceci :

screen

vu ? ton problème, il est là : chaque fois qu'il y a une valeur 0 ou -1 à la place d'une formule ; et comme c'est pas la macro qui a fait ça, ça veut dire que c'était déjà comme ça avant exécution de la macro, donc ça signifie aussi que t'as pas étiré correctement la formule de K2 sur toute la hauteur de la colonne K ; ou si tu l'as fait, alors après, tu as écrasé quelques formules par une valeur 0 ou -1.

solution : avant exécution de la macro, refais correctement ta colonne K : étire la formule de K2 jusqu'en bas ; fais Ctrl e ; vérifie les résultats, qui devraient être bons.

dhany

* dans ma macro, j'ai oublié l'instruction qui évite la mise à jour de l'écran ➯ la macro est exécutée plus rapidement,

et l'affichage de l'écran final sera instantané au lieu d'être progressif

* ajoute Application.ScreenUpdating = 0, comme ceci :

Option Explicit

Sub InsertLine()
  Dim n&: n = Cells(Rows.Count, 11).End(3).Row
  If n = 1 Then Exit Sub
  Dim Tbl, i&: Tbl = [K1].Resize(n): Application.ScreenUpdating = 0
  For i = n - 1 To 2 Step -1
    If Tbl(i, 1) = -1 Then Cells(i + 1, 6).Resize(, 6).Insert 2, 1
  Next i
End Sub

c'est déjà fait dans ce fichier :

2exemple-court.xlsm (28.39 Ko)

dhany

Bonjour @dhany,

C'est loupé, l'erreur n'est pas là ! C'est moi qui ai rajouté les 0 et -1 dans le fichier ce que j'obtiens pour te montrer réellement ce que j'obtiens (quand c'est sur un petit tableau ça marche, mais quand c'est sur mon tableau avec des centaines de milliers de lignes ça ne marche pas et le fichier est trop lourd pour que je puisse te le poster ici donc j'ai simulé ce que j'obtiens).

Donc mon problème est toujours là

Marjow

Salut Marjow54,

Salut l'équipe,

il ne te reste plus qu'à supprimer 109.000 lignes de ton fichier et de nous l'envoyer après anonymisation éventuelle avec une deuxième feuille corrigée manuellement sur quelques -1...

Un indice pour nous repérer : le fond des tableaux, très chaotique, que l'on obtient tous les 3 est-il ce que tu attends ?

A+

Bonjour,

Le fichier que tu demandes est celui qui doit être dans les messages précédant avec feuille 2 ce que je veux et feuille 3 ce que j'obtiens).

Oui les tableaux que vous obtenez sont ceux que je souhaites avoir mais le problème que je rencontre est que quand j'exécute la macro sur mon tableau super grand (qui est identique à celui sur lequel @dhany travaille, donc sans modifier la macro), il y a ce décalage qui apparait, alors que sur un petit tableau ca marche.

MArjow54

Je pense avoir compris mon soucis : je pense que c'est parce qu'il fait du bas vers le haut, je m'explique. Admettons que le premier -1 rencontré est en 1610, il ajoute un ligne en 1611. Le deuxième est en 1384, il ajoute donc une ligne en 1385. Le problème est donc que ma première ligne insérée n'est plus en 1611 mais en 1612. Donc lorsque la troisième ligne est insérée, ca décalle en 1613 et ainsi de suite à mesure qu'on remonte dans le tableau. Et vu la grandeur de mon tableau (110000 lignes) forcément tout se décale !

N'y a-t-il vraiment pas un moyen de faire la boucle du haut vers le bas ? Il faudrait vraiment que ca traite ligne par ligne, quand on rencontre un -1 hop ca crée une ligne et on continue à la ligne suivante.

sinon je vais mettre encore un peu de cervelle pour trouver une autre méthode...

Merci à tous

Rechercher des sujets similaires à "insertion vba"