Modifier une cellule en fonction de son N° de ligne

Modeste

XLDnaute Barbatruc

• 22 Décembre 2011

• #2

Re : Modifier une cellule en fonction de son N° de ligne

Bonsoir eric57 et bienvenue sur les rives enchanteresses d'XLD 😛

Je ne suis pas plus certain qu'en bien d'autres occasions d'avoir compris ... un essai en attendant ... et puis tu nous diras!

En AE1 (par exemple) indiquer la formule suivante:

Code:

=MAX(SI($B$2:$B$20000<>"";LIGNE($B$2:$B$20000)))

Formule matricielle à valider avec Ctrl+Shift+Enter (des accolades doivent alors "entourer" ta formule et le résultat affiché devrait correspondre à la dernière cellule non-vide de la colonne B: 1191, dans ton exemple)

En G2, cette fois, essaie

Code:

=SI(LIGNE()=2;J1&B2&D2;SI(LIGNE()>$AE$1;"";SI(LIGNE()=$AE$1;$N$1;SI(NB.SI($R$1:$AC$1;LIGNE());B2&$L$1;B2&D2))))

Cette formule-ci n'est pas matricielle: un simple Enter permet de valider.

Recopie cette dernière formule sur un nombre de lignes au choix (2000, par exemple et au moins 1191, bien sûr!).

Si tu évoques un bouton, c'est que tu penses à une macro ... Voyons déjà si ce que j'ai compris est correct 😕

 

Vote positif 0 Vote négatif

eric57

XLDnaute Occasionnel

• 22 Décembre 2011

• #3

Re : Modifier une cellule en fonction de son N° de ligne

Bonsoir Modeste et merci pour cette réponse.

J'ai fait selon tes formules, et cela marche à 2 exceptions prêt.

La dernière ligne ne reprenait pas la valeur contenu dans la dernière ligne de B.

J'ai donc modifié ainsi ta formule qui donne

=SI(LIGNE()=2;J1&B2&D2;SI(LIGNE()>$AE$1;"";SI(LIGNE()=$AE$1;$b2&$N$1;SI(NB.SI($R$1:$AC$1;LIGNE());B2&$L$1;B2&D2))))

Cliquez pour agrandir...

Et c'est parfaitement fonctionnel. Merci pour ces 2 formules .

Dans le tableau, je parlais aussi d'un bouton "copier". En faite il en faudrait 2

1 premier bouton pour "vider" la colonne B avant de venir coller une nouvelle liste, ceci pour être sur qu'une nouvelle liste + courte que la précédente, ne soit pas faussée par des lignes existantes.

1 deuxième bouton pour "copier" dans le presse papier le contenu de la colonne G (de la cellule G2 à la fin du tableau

Il faudrait aussi qu'en fermant le classeur, on vide automatiquement la colonne B afin de retrouver une colonne vide à l'ouverture.

En tout cas, un grand merci pour cette avancée qui me fera gagner du temps.

 

Vote positif 0 Vote négatif

Modeste

XLDnaute Barbatruc

• 22 Décembre 2011

• #4

Re : Modifier une cellule en fonction de son N° de ligne

Re-Bonsoir,

Deux petits bouts de code à associer à tes boutons:

VB:

Sub viderColB()
Sheets(1).Range("B2:B" & Sheets(1).Range("B" & Rows.Count).End(xlUp).Row).Clear
End Sub

VB:

Sub Copier()
Sheets(1).Range("G2:G" & Sheets(1).Range("G" & Rows.Count).End(xlUp).Row).Copy
End Sub

Le premier code est éventuellement à recopier dans l'objet ThisWorkbook, lié à l'événement Workbook_BeforeClose

 

Vote positif 0 Vote négatif

eric57

XLDnaute Occasionnel

• 22 Décembre 2011

• #5

Re : Modifier une cellule en fonction de son N° de ligne

Voila bien bien longtemps que 'avais pas mis le nez dans visual basic.....

Pour résumer, j'ai réussi a créer les 2 modules "vider " et "copier" Ensuite j'ai crée 2 formes auquelles j'ai attribué a chacune la macro
Ce qui peut peut-être paraitre "basic" mais pourtant pas très "visual" pour moi (Désolé j'ai pas pu m’empêcher de la faire celle -la)

La fonction "vider fonctionne bien, meme trop bien car si je clique 2 fois ou lorsque la colonne est déjà vide, la cellule B1 s'efface aussi. Ce qui n'est pas génant puisque le titre ne me sert plus a grand chose.

La fonction "copier" a un inconvénient, il copie la totalité de la colonne "G" qui contient non seulement le résultat visible, mais aussi les lignes contenant les formules, soit 8000 lignes. Lorsque je "colle" j'ai donc 8000 lignes même si elles sont vides pour celles qui ne contenaient que la formule

La fonction

Le premier code est éventuellement à recopier dans l'objet ThisWorkbook, lié à l'événement Workbook_BeforeClose

Cliquez pour agrandir...

La je sèche.

J'ai bien trouvé une ligne marqué ThisWorkbook dans la fenêtre visualbasic, j'ai double-cliqué dessus, ensuite j'ai trouvé une fenêtre avec "explorateur d'objets" et workbook puis à coté beforeClose mais je ne sais pas quoi faire après ?

 

Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc

• 22 Décembre 2011

• #6

Re : Modifier une cellule en fonction de son N° de ligne

Bonsoir à tous

Félicitations pour ton 2K, Modeste 😉
(Je t'ai une une spécial dédicace en zik dans le salon ( dans le fil de JCGL)

Ce soir soyons radical
Sub viderColB()
Sheets(1).Columns(2).delete
End Sub

Merdre de Merdre, c'est trop radical

Bon, bah, soyons radical-mou alors
Sub viderColB()
Sheets(1).Columns(2)=Empty
End Sub

eric57 à dit:

La je sèche.
J'ai bien trouvé une ligne marqué ThisWorkbook dans la fenêtre visualbasic, j'ai double-cliqué dessus, ensuite j'ai trouvé une fenêtre avec "explorateur d'objets" et workbook puis à coté beforeClose mais je ne sais pas quoi faire après ?

Cliquez pour agrandir...

Regarde la pièce jointe 835357
source image: veriti (merci à lui)

 

Pièces jointes

• Modules02.gif

  3 KB · Affichages: 98

Dernière édition: 22 Décembre 2011

Vote positif 0 Vote négatif

eric57

XLDnaute Occasionnel

• 22 Décembre 2011

• #7

Re : Modifier une cellule en fonction de son N° de ligne

Bonsoir Staple1600

Oui j'ai bien trouvé ce que tu indique par la flèche, mais après .... je fais quoi ? je colle le code

VB:

 Sub viderColB()
Sheets(1).Columns(2)=Empty
End Sub

Mais ça j'ai pas l'impression que sa marche

 

Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc

• 22 Décembre 2011

• #8

Re : Modifier une cellule en fonction de son N° de ligne

BonsoiRE


Il faut mettre le code comme ceci

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets(1).Columns(2) = Empty
End Sub

(Dans la zone blanche à droite de l'écran)

 

Vote positif 0 Vote négatif

Modeste

XLDnaute Barbatruc

• 22 Décembre 2011

• #9

Re : Modifier une cellule en fonction de son N° de ligne

re²,

"Ah grafe", quel plaisir de te croiser! Je passe au salon tout de suite après.

Pour Eric et en complément aux propositions de l'honorable Staple1600, je pensais récupérer le n° de ligne qui figure en AE1 (et auquel je n'ai plus pensé ... c'est benoît, hein 😱) ... en tout cas, si on voulait conserver le titre en ligne 1 dans le viderColB (et puis ça règle aussi le problème du copier trop "étendu"

Les 2 bouts de code deviendraient:

VB:

Sub viderColB()
Sheets(1).Range("B2:B" & Application.Max(2, Sheets(1).Range("AE1").Value)).Clear
End Sub

VB:

Sub Copier()
Sheets(1).Range("G2:G" & Sheets(1).Range("AE1").Value).Copy
End Sub

... à tester, of course!

 

Vote positif 0 Vote négatif

eric57

XLDnaute Occasionnel

• 22 Décembre 2011

• #10

Re : Modifier une cellule en fonction de son N° de ligne

Ha ben non la pour le coup ca fonctionne plus .... 🙁

Erreur 13 pour la fonction "vider" et erreur 1004 pour la fonction "copier"

 

Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc

• 22 Décembre 2011

• #11

Re : Modifier une cellule en fonction de son N° de ligne

REBonsoirRE

Il faudrait aussi qu'en fermant le classeur, on vide automatiquement la colonne B afin de retrouver une colonne vide à l'ouverture.

Cliquez pour agrandir...

C'est bien ce que tu veux eric57 ou tu veux garder l'entête en B1.

Car Modeste prends des gants mais pas moi.

Mon code vide toute la colonne B sans prévenir.

 

Dernière édition: 22 Décembre 2011

Vote positif 0 Vote négatif

eric57

XLDnaute Occasionnel

• 22 Décembre 2011

• #12

Re : Modifier une cellule en fonction de son N° de ligne

oui on peut vider toute la colonne B ce n'est pas grave.

(javais bien noté la différence de style 🙂) )

 

Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc

• 22 Décembre 2011

• #13

Re : Modifier une cellule en fonction de son N° de ligne

Re



Donc tu as réussi à mettre le code dans ThisWorkBook?

 

Vote positif 0 Vote négatif

eric57

XLDnaute Occasionnel

• 22 Décembre 2011

• #14

Re : Modifier une cellule en fonction de son N° de ligne

Oui j'ai reussi, mais les dernières modifs de Modeste me bloque le reste.

Je vais garder la fonction vider elle marche bien ,
Juste le problème de la fonction "coller" qui ne doit prendre en compte que le nombre de ligne déclarée en AE1

 

Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc

• 22 Décembre 2011

• #15

Re : Modifier une cellule en fonction de son N° de ligne

Re


Juste pour me faire plaisir, un petit coup d’endives en passant.
Sub Copier()
With Sheets(1)
.Range("G2").Resize(.[AE1]).Copy
End With
End Sub

 

Vote positif 0 Vote négatif