Recopier liste en supprimant les lignes "vides" mais contenant une formule

Bonjour Ronan,

quand j'ai cliqué sur ton lien, ça demande de s'inscrire sur le site
pour pouvoir accéder à ton fichier ; j'ai donc renoncé ; mais si
tu joins ton fichier via cjoint.com, je chercherai une solution.

rhodo

Bonjour Rhodo, merci pour ton message. J'ai voulu envoyer mon fichier par cjoint.com, mais je n'aime pas trop mettre les coordonnées de ma carte bancaire. J'ai mis mon fichier sur Dropbox, tu devrais pouvoir cette fois le récupérer sans problème. Merci de m'aider sur ce sujet et bon week end à toi.

https://www.dropbox.com/scl/fi/h5ne1miir5141evbksdyd/Cave-copie.xlsm?rlkey=qh2q0pd2nfiqua6b3os1nawnh&dl=0

@Ronan

tu as écrit :

« J'ai voulu envoyer mon fichier par cjoint.com, mais je n'aime pas trop mettre
les coordonnées de ma carte bancaire. »

c'est bizarre : quand j'utilise cjoint.com, ça ne me demande jamais de mettre
les coordonnées de ma carte bancaire ; si ça le faisait, alors comme toi,
je n'aimerais pas que ça le fasse.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

j'ai suivi ton lien Dropbox, mais ça me met ceci : image

j'utilise Google, mais je ne veux pas utiliser Dropbox, ni Apple ; comme il y a
« ou E-mail », j'ai essayé en mettant mon email perso, mais ensuite, ça me
demande de m'inscrire ; j'ai cliqué sur le lien qui mène sur les conditions
d'utilisations, et il y a des choses qui ne me plaisent pas ➯ j'ai renoncé.

rhodo

Merci, bien compris ! Du coup, je t'envoie un lien du fichier qui est sur Google Drive, j'espère que cela sera plus simple pour toi: https://docs.google.com/spreadsheets/d/1ywtVhJK61I_gwGMgW2N7geTpU4ljfMS1/edit?usp=sharing&ouid=101441257475852192135&rtpof=true&sd=true

Si tu veux des explications complémentaires, n'hésites pas, bonne soirée

@Ronan

j'ai écrit : « j'utilise Google » ; j'aurais dû écrire plus précisément : « j'utilise
le navigateur Google Chrome, car je n'utilise ni Google Drive, ni Google
Sheets ; j'ai le tableur Excel 2021 (avec VBA) ; je n'utilise pas non plus
Microsoft OneDrive. »

j'ai suivi ton lien Google, mais ça me met ceci : image

j'ai cliqué sur le bouton "Demande d'accès", mais pour la suite : ça me
demande mon adresse mail perso, et je ne veux pas la saisir car pour
l'envoi d'un mail de demande d'accès ou pour la réception d'un mail
de confirmation, je ne veux pas communiquer mon email perso.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour cjoint.com, si ça te demande les coordonnées de ta carte bancaire,
c'est peut-être car tu as un virus sur ton PC qui simule le site cjoint.com ?
(afin de faire des tentatives de phishing)

vérifie dans la barre d'adresse de ton navigateur que tu as bien "cjoint.com"
ou "https://www.cjoint.com" ; avec ton antivirus, fais une analyse complète
de ton PC ; remarque : si tu déposes ton fichier sur cjoint.com, tu n'as pas
besoin de mettre un mot de passe car seuls ceux à qui tu passes le lien
de téléchargement peuvent accéder à ton fichier  "Cave copie.xlsm".

bonne soirée à toi aussi.

rhodo

@Ronan

dans ton énoncé, tu as écrit : « dans l'onglet "listedevins", j'ai en colonne i
une liste de valeurs avec quelques cellules sans texte mais contenant une
formule. »

sans avoir téléchargé ton fichier, j'ai vu que la colonne i est "NOMBRE
DE BOUTEILLES CONSOMMÉES" ; il n'y a pas de texte dans cette
colonne, effectivement ; mais il n'y a pas non plus de cellule vide ; il n'y
a que des nombres, dont certains sont 0 ; alors si y'a des formules,
je suppose que tu veux « la liste dans une colonne voisine » sans ces
nombres nuls 0 ; car dans ton énoncé, il y a : « Comment faire pour
recopier cette liste dans une colonne voisine sans ces fameuses
cellules (vides) ? » (sauf que c'est pas des cellules vides vu que
c'est des cellules avec le nombre nul 0).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

« la colonne voisine », ça serait quelle colonne de destination au juste ?
ça serait plus simple si tu précisais ; par exemple : « la colonne BH ».

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

il manque une information TRÈS IMPORTANTE dans ton fichier :

« À CONSOMMER AVEC MODÉRATION »

pas vrai ?  ;)  même si ça sera bientôt Noël, évite de boire d'un coup
toutes les bouteilles de ta cave à toi tout seul ! sinon, tu ne seras
plus en état de poster sur ce forum.  ;)

rhodo

@Ronan

rectification : selon tes données, c'est la colonne J qui contient une formule,
pas la colonne i ! car colonne G : "NOMBRE DE BOUTEILLES ACHETÉES" ;
colonne i : "NOMBRE DE BOUTEILLES CONSOMMÉES" ; colonne J :
"NOMBRE DE BOUTEILLES RESTANT EN CAVE".

j'ai pas vu de formule, mais je propose en J2 : =SI(OU(G2="";I2="");"";G2-I2)
(formule à recopier vers le bas jusqu'en dernière ligne utilisée).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

comme il n'y a pas besoin de formules en colonne i, tu voudrais ceci :
la liste des lignes pour lesquelles il n'y a pas 0 en colonne J ; c'est
donc la liste des bouteilles dont le stock est nul = à commander.

ou p't'être au contraire les lignes pour lesquelles il y a 0 en colonne J
= la liste des bouteilles dont le stock n'est PAS vide = la liste des
bouteilles que tu peux vendre aux clients.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour l'un ou pour l'autre, tu pourrais obtenir le résultat voulu sans VBA,
en utilisant le filtre automatique ; c'est sur l'onglet Données, groupe
"Trier et filtrer", commande "Filtrer" (voir l'exemple ci-dessous).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

sélectionne par exemple A1 ; clique sur la commande "Filtrer" ➯
ça met des petites flèches bas sur la ligne des en-têtes.

sélectionne J1 "NOMBRE DE BOUTEILLES RESTANT EN CAVE" ;
dans le coin bas droit de la cellule, clique sur la petite flèche bas ;
dessous, décoche 0 et clique sur OK ➯ tu as la liste des bouteilles
que tu peux vendre aux clients (les lignes avec 0 sont masquées).

toujours en J1, clique sur la même petite flèche bas ; clique 2× sur
"Sélectionner tout" ➯ ça coche le 0, puis ça décoche tout ; clique
1× sur 0 ➯ ça coche 0 ; clique sur OK ➯ liste des bouteilles qui
sont épuisées (stock 0) = à commander (seules les lignes avec 0
apparaissent, les autres sont masquées).

pour voir de nouveau toutes les lignes du tableau, 2 solutions :
a) clique sur "Sélectionner tout" ➯ tout est coché ; clique sur "OK" ;
b) clique de nouveau sur la commande "Filtrer" ➯ ça enlève le
mode "Filtre" (bascule) ➯ sur la ligne des en-têtes, il n'y a plus
de petites flèches bas, et toutes les lignes masquées sont de
nouveau visibles.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour plus de détails sur l'utilisation du filtre, fais une recherche Google.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour ta colonne F "DATE D'ACHAT" : j'ai vu que c'est d'abord le mois puis
le jour, et l'année ; je propose ce format de nombre : "MM/JJ/AAAA".

attention : dans cette colonne F, tu as à la fois de vraies dates et des
dates qui semblent vraies (mais qui ne le sont pas).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

au cas où tu n'as pas envie d'utiliser le filtre automatique, et que tu veux
absolument une solution VBA (via une macro) :

sans avoir téléchargé ton fichier, j'ai pu récupérer quelques données ;
dans le post suivant : solution VBA.  :)

rhodo

Bonjour Ronan,

Lis d'abord mes 3 posts précédents.  :)

lien fichier : https://www.cjoint.com/c/MKAaENSKub0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* à l'ouverture du fichier, tu es sur la 1ère feuille "liste de vins".

* regarde la plage N1:Q5 ; c'est ton tableau de droite, avec une présentation
un peu améliorée.  :)  comme ce tableau droit est un autre tableau que le
tableau principal gauche, c'est une très mauvaise idée que tu l'aies collé
tout contre dans ton fichier initial ! c'est pourquoi j'ai inséré une colonne
pour les séparer : la colonne M (qui est vide).

* regarde le texte de la cellule F1 ; j'ai ajouté "(MM/JJ/AAAA)" pour bien
préciser que c'est : 2 chiffres pour le Mois ; 2 chiffres pour le Jour ; et
4 chiffres pour l'Année ; éventuellement, tu peux le remplacer par
"(MM/DD/YYYY)" si tu préfères en style anglais.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

va sur la 2ème feuille "vins à commander" ; note que sous la ligne 1 des
en-têtes, c'est entièrement vide ; retourne sur la 1ère feuille ; fais Ctrl e.

ça va sur la 2ème feuille ; tu dois voir 8 lignes de données ; pour chacune,
en colonne J , il y a 0 : c'est la liste des vins épuisés, à commander.

extrait (il manque seulement la partie droite) : image 1

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

va sur la 3ème feuille "vins en stock" ; sous la ligne 1 des en-têtes, c'est
entièrement vide ; retourne sur la 1ère feuille ; fais Ctrl f.

ça va sur la 3ème feuille ; tu dois voir plusieurs lignes de données ; pour
chacune, en colonne J, il y a un nombre > à 0 : c'est la liste des vins en
stock, que tu peux vendre aux clients.

extrait (il manque la partite droite et le bas) : image 2

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

attention : si tu modifies des données en 1ère feuille, tu ne devras pas oublier
de refaire la même manip pour actualiser la feuille de résultats ; mais inutile
d'effacer les anciens résultats, car c'est fait automatiquement par la macro.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

fais Alt F11 pour voir le code VBA de Module1 (58 lignes) ; bonne lecture !  ;)

fais de nouveau Alt F11 pour revenir sur Excel.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

si besoin, tu peux demander une adaptation ; à te lire pour avoir ton avis.  :)

rhodo

Bonjour,

Tout d'abord, un grand merci pour avoir réfléchi sur mon problème... J'ai pu grâce à ton lien mettre mon fichier sur cjoint.com, j'espère que tu vas enfin pouvoir y accéder :

https://www.cjoint.com/c/MKAiibKImKv

J'essaye de mieux t'expliquer comment je vois les choses. Dans l'onglet "Achats", je saisis les bouteilles achetées avec les différents critères voulus (nom, couleur, année, prix, quantité...). Le nombre de bouteilles consommées et le nombre restant en cave s'actualisent automatiquement. L'onglet "Plan de cave" me permets de savoir où sont rangées mes bouteilles. L'onglet "Dégusté le" me permets de saisir les bouteilles que j'ai ouvert avec mes amis ou ma famille (avec modération ? ou pas...) en utilisant dans la colonne B ma liste déroulante dont la source est située dans l'onglet "Liste de vins" dans la colonne i. Dans l'onglet "Liste de vins", je récupère en colonne A par copie les vins achetés enregistrés dans "Achats", avec le stock en colonne B. Puis je les mets par ordre alphabétique avec une formule index équiv en colonne E. Pour simplifier la saisie dans l'onglet "Dégusté le" où j'ai ma liste déroulante dont la source est bien en colonne i de l'onglet "Liste de vins", je voulais éviter d'avoir les vins dont le stock est à 0, d'où l'idée de re travailler la colonne i ailleurs pour ne pas avoir les cellules avec des blancs qui correspondent à des vins que je n'ai plus, ce qui à force va rendre la lisibilité de la liste déroulante plus compliquée parce que pleine de "trous".

Je ne sais pas si c'est assez clair, et si l'on peut échanger plus directement (?) au cas où... Je veux bien aussi ton avis sur ce fichier, bon dimanche, en attendant, je trinquerai à ta santé tout à l'heure ! Merci !

Bonjour Ronan,

ton fichier en retour : https://www.cjoint.com/c/MKAxI6SUO80

à l'ouverture du classeur, tu es sur la 1ère feuille "Achats" ; j'en parlerai un peu
plus loin, sous le long trait, alors ne change pas de feuille.

y'a 5 feuilles au lieu de 6, car j'ai supprimé la feuille "Listedevins" ➯ c'est normal
que plus rien dans le classeur ne s'y rapporte ; j'ai supprimé le nom défini Noms,
qui faisait référence à : "=Listedevins!...".

j'ai supprimé aussi tes 3 noms définis "Années" ; "Années_Rouge" ; "Ans" car
pour chaque, y'avait : Valeur : #REF! ; Fait référence à : =#REF!#REF!

il reste donc ces 2 noms définis : Vins et Zone_d_impression ; fais Ctrl F3 ➯
fenêtre "Gestionnaire de noms" ; ça allège, hein ?  ;)

-----------------------------------------------------------------------------------------------------------------

feuille "Achats"

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

j'ai inséré une colonne entre ton tableau principal de gauche et ton tableau
droit : la colonne M, qui est vide ; je te rappelle que c'est mieux d'éviter de
les coller l'un à l'autre.

par exemple, si tu voulais utiliser le filtre automatique sur ton tableau de
gauche, c'est grâce à la colonne vide M que : a) Excel comprendra que
le tableau gauche et le tableau droit sont séparés ; b) sur la ligne 1 des
en-têtes, ça mettrait des petites flèches bas en A:L et pas en A:Q.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour la plage N1:Q5 : j'ai un peu amélioré la présentation ; j'ai aussi changé
toutes les formules ; toutes ? non ! seule la formule de O5 a su me résister :
tu avais dû entrer un peu de potion magique dans sa composition.  ;)

en O3 : =SOMME.SI(C:C;"Rouge";J:J)

en P3 : =SOMME.SI(C:C;"Blanc";J:J)

en Q3 : =SOMME.SI(C:C;"Champagne";J:J)

pour ces trois formules, tu avais utilisé SOMME.SI.ENS(), comme dans :
=SOMME.SI.ENS(J:J;C:C;"Rouge") ; c'est inutile, car y'a qu'une seule
condition !

en N3 : =O3+P3+Q3

tu avais mis =SOMME(N2:P2) ; ça marche, mais pour additionner seulement
trois éléments, c'est inutile d'utiliser la fonction SOMME() ; sauf si tu avais
l'intention d'insérer au moins une autre colonne entre, mais je pense que
ce n'est pas le cas.  ;)

en P5 : =MIN.SI.ENS(D:D;J:J;">0";C:C;"Blanc")

en Q5 : =MIN.SI.ENS(D:D;J:J;">0";C:C;"Champagne")

là, j'ai simplement enlevé les « $ » qu'il y avait avant ; elle sont donc
aussi simples que ta formule gauloise au vin rouge :

en O5 : =MIN.SI.ENS(D:D;J:J;">0";C:C;"Rouge")

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

si tu as changé la cellule active, sélectionne Q8 (ou une autre cellule vide).

onglet Accueil, groupe Styles, Mise en forme conditionnelle, Gérer les règles...
➯ fenêtre "Gestionnaire des règles de mise en forme conditionnelle".

au lieu de "Sélection actuelle", choisis "Cette feuille de calcul" ➯ ça montre
toutes les règles de MFC pour la feuille "Achats" ; comment ? y'en n'a plus
que 3 ? elles sont passées où, toutes les autres ? ne t'inquiètes pas, c'est
normal, et ça fait la même chose qu'avant ; j'ai seulement simplifié un peu
tout le toutim ! je t'assure que tu n'avais pas besoin de 14 MFC ! alors j'en
ai seulement supprimé 11.  ;)  pour les 3 MFC qui restent, tu peux voir
aisément : le champ "S'applique à" ; la Formule, sauf la dernière, que
je mets donc ici en entier :

=ET($J2>0;OU(ET($C2="Rouge";$D2=$O$5);ET($C2="Blanc";$D2=$P$5);
ET($C2="Champagne";$D2=$Q$5)))

la formule est un petit peu plus courte, car j'ai « factorisé » le test "$J2>0",
qui n'apparaît plus qu'une seule fois (au lieu de 3 fois avant).  ;)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ta formule initiale en i2 :

=SOMME.SI(Dégusté_le!$B$2:$B$10000;"=Château Lusseau 2014";
Dégusté_le!$C$2:$C$10000)

en dessous, c'est la même formule à part le nom du vin qui change ;
exemples en i3, i4, i5, et i6 :

=SOMME.SI(Dégusté_le!$B$2:$B$10000;"=Château Cap Saint Martin 2015";
Dégusté_le!$C$2:$C$10000)

=SOMME.SI(Dégusté_le!$B$2:$B$10000;"=Lyseras 2017";
Dégusté_le!$C$2:$C$10000)

=SOMME.SI(Dégusté_le!$B$2:$B$10000;"=Confidences Prieuré Lichine 2015";
Dégusté_le!$C$2:$C$10000)

=SOMME.SI(Dégusté_le!$B$2:$B$10000;"=La réserve de Malartic 2014";
Dégusté_le!$C$2:$C$10000)

oh la la, mon pauvre ami ! même si tu as sans doute copié/collé la formule de i2,
tu t'es embêté pour rien à changer le nom du vin pour chaque ligne !  :(

voici la nouvelle formule de i2 (déjà étirée vers le bas) :

=SI(A2="";"";SOMME.SI(Dégusté_le!$B$2:$B$10000;"=" & A2;
Dégusté_le!$C$2:$C$10000))

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

je sais que toutes les formules sont bonnes, et pourtant, j'ai vu une différence
de résultat en O3 : ça retourne 63 au lieu de 61 avant ; et bien sûr, en N3, il y a
146 au lieu de 144 avant ; alors que se passe-t-il ? au début, j'ai cru que j'avais
modifié un nombre par mégarde, mais ce n'est pas ça.  ;)

regarde A9 ; tu vois le texte "Bellissima" ; mais en fait, c'est "Belissima " :
tu as mis un espace final en trop !  :(  du coup, le vin n'est plus reconnu,
et le résultat en i9 est atteint de delirium tremens : ça retourne 0 au lieu
de 2 !  :(  en A9, enlève l'espace fautif et ça sera ok :

en A9 : "Bellissima" au lieu de "Bellissima " ➯ en i9 : 2 ; en J9 : 0 ;
en O3 : 61 ; en N3 : 144.  :)

pour éviter qu'un problème similaire arrive de nouveau, tu dois être
plus rigoureux dans ta saisie des données !  ;)

j'avais déjà corrigé A50 : "Naudé-23" au lieu de "Naudé 23" ; mais
j'viens d'voir c'que j'ai décrit dans cette dernière partie de ce post.

(et c'est après avoir déposé le fichier Excel sur cjoint.com)

-----------------------------------------------------------------------------------------------------------------

c'est tout pour l'instant ; la suite sera dans le prochain post.  :)

rhodo

@Ronan

feuille "Plan_de_Cave" : je n'ai rien modifié, et dans ton fichier initial, il y avait
déjà la valeur d'erreur #REF! en X2 ; Y2 ; et Z2 ; je ne sais pas quelle formule
tu avais mis avant en X2 et Y2, mais en Z2, tu peux mettre =X2+Y2 au lieu de
=SOMME(X2:Y2) ; d'autre part, de la même façon que tu as mis en X1 et Y1
"Rouge" et "Blanc", tu devrais peut-être ajouter "Champagne" ? et idem sur
toute la feuille pour prendre en compte ce vin du 3ème type ; un vin SF ? de
Monsieur Spock peut-être ?  ;)  en tout cas, merci d'avoir trinqué à ma
santé, c'est sympa !  :)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

feuille "Listedevins" : ah non, c'est vrai qu'elle n'existe plus !  ;)  alors inutile
de la chercher : tu ne la trouveras pas dans les feuilles masquées ; ni dans
les feuilles cachées, ni dans les feuilles très cachées, ni dans les feuilles
transparentes, ni dans les feuilles éthérées ou vaporeuses ; peut-être
dans les feuilles célestes ?  ;)  il paraît qu'une feuille supprimée va tout
droit au paradis, et pas dans la corbeille de Windows (si l'utilisateur s'est
comporté correctement, durant toute sa vie d'internaute).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

feuille "Tarifs" : j'ai seulement amélioré un peu la présentation, rien d'autre ;
et après, je n'ai pas oublié de remettre la protection de la feuille.  ;)

note qu'en D1 et H1, c'est mieux de mettre "OÙ" au lieu de "OU" : dessous,
les données concernent des lieux, PAS des choix.  ;)

pour mettre un « Ù » : fais Alt 0217 (nombre à taper sur le pavé numérique) ;
tu peux aussi trouver cette lettre via charmap (Table des caractères).

note que les prix de C sont plus lisibles quand ils ne sont pas collés contre
les lieux de D ; de la même façon, les prix de i sont plus lisibles quand ils
ne sont pas collés contre les ordres de J ; à propos, en J1, c'est "ORDRE"
et pas "ORDRE &".  ;)  (à moins qu't'aies oublié kek'chose à droite ?)

--------------------------------------------------------------------------------------------------------------

la suite au prochain épisode.  ;)

rhodo

@Ronan

feuille "Paramètres" (partie 1)

j'ai changé un petit peu la présentation, et tu vois nettement que c'est ici que
j'ai placé la liste des vins dont le stock n'est pas nul ; mais avant de le voir
en détail, voyons déjà ce que tu avais fait au départ.  :)

--------------------------------------------------------------------------------------------------------------

ta formule en B2 : =SI(A2>0;1)

ta formule en B3 : =SI(A3<>0;$B2+1;"")
(étendue vers le bas jusqu'en B100)

si j'avais choisi de laisser cette colonne de nombres, j'aurais plutôt
mis à la place une seule formule en B2 : =SI(A2>0;LIGNE()-1;"")
(étendue vers le bas jusqu'en B100)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ta formule en C1 : =RECHERCHE(9^9;B:B) ➯ 32

ma formule en B2 : =NBVAL(A:A)-1 ➯ 32

comme je trouve bien le même nombre d'appellations que toi, j'ai trouvé
inutile de laisser la colonne de nombres susmentionnée ➯ je l'ai tout
simplement supprimée ; elle est partie rejoindre la feuille "Listedevins"
au paradis céleste des déchets informatiques à recycler ; c'est p't'être
Saint-Pierre qui s'occupe de leur donner une cure de jouvence ?  ;)

--------------------------------------------------------------------------------------------------------------

le bouton "Tri Alphabétique" n'appelle plus la macro Tri_AlphabŽtique()
car je l'ai renommée plus simplement en Tri_Alpha() ; et j'en ai changé
tout le code.

--------------------------------------------------------------------------------------------------------------

ta sub initiale était celle-ci (sans compter les commentaires : 16 lignes) :

Sub Tri_AlphabŽtique()
'
' Tri_AlphabŽtique Macro
'

'
    Columns("A:B").Select
    ActiveWorkbook.Worksheets("Paramtres").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Paramtres").Sort.SortFields.Add2 Key:=Range( _
        "A2:A100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Paramtres").Sort
        .SetRange Range("A1:B100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C5").Select
End Sub

mis à part ton caractère « Ž » bizarre, il y a 2 grosses erreurs !  :(

a) il y a 3× Worksheets("Paramtres") au lieu de Worksheets("Paramètres")
➯ ton code va planter car il ne trouvera PAS la feuille "Paramètres" !

b) tu tries A:B alors que la colonne B contient les formules vues plus haut ;
vu que c'est des formules, il ne faut PAS les trier : tu aurais dû trier
seulement A ! c'est seulement si B avait contenu des données (pas des
formules) qu'il aurait fallu trier A:B (pour que les données de B suivent
les données de A lors du tri, et restent en coïncidence sur la même ligne).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ma sub est un peu plus courte (7 lignes) :

Sub Tri_Alpha()
  Dim d&: Application.ScreenUpdating = 0
  With Worksheets("Paramètres")
    d = .Cells(Rows.Count, 1).End(3).Row
    If d > 2 Then .Range("A2:A" & d).Sort .[A2], 1
  End With
End Sub

elle fait le tri nécessaire, et n'agit que sur la colonne A ; et accessoirement,
mon code VBA trouvera bien la feuille "Paramètres".  ;)  (tu peux essayer,
sans aucun souci : ça ne plantera pas)

--------------------------------------------------------------------------------------------------------------

les lieux sont maintenant en colonne F (et plus en colonne H) ; et j'ai ajouté
l'en-tête "LIEUX" (en F1) ; bien évidemment, sur la feuille "Achats", pour la
colonne K "EMPLACEMENT", la Source de la Validation des données est
devenue : =Paramètres!$F$2:$F$11 au lieu de =Paramètres!$H$2:$H$11.

et encore, je simplifie, car dans ta colonne K, ce n'était pas homogène :
dans ton ancien fichier, sélectionne K2:K53 ; clique sur le bouton de
Validation des données ; message d'Excel :

« La sélection contient des cellules sans paramètre de validation de données.
Voulez-vous étendre la validation de données à ces cellules ? »

charmant, n'est-ce pas ?  ;)

--------------------------------------------------------------------------------------------------------------

petit bonus : fais Ctrl F2 ➯ aperçu avant impression : tu peux voir que ça tient
sur une seule page (et c'est prêt à être imprimé) ; alors ne modifie pas la
largeur des colonnes inconsidérément.  ;)

autre petit bonus (sur la feuille "Achats") :  fais Ctrl F2 ➯ aperçu : en largeur,
le tableau principal gauche tient sur une seule page (mis à part le 2ème petit
tableau annexe qui est sur le côté droit).

--------------------------------------------------------------------------------------------------------------

pour la partie 2 de la feuille "Paramètres", ce sera une autre fois.  ;)
(ce soir ou peut-être demain, selon mes disponibilités)

ne cherche pas de formules en colonne D : y'en n'a aucune.  ;)

--------------------------------------------------------------------------------------------------------------

j'espère que tu n'auras pas oublié de lire mes 2 posts précédents,
de 02:16 et 04:32.  ;)

rhodo

Bonjour Rhodo, d'abord, merci pour toutes tes remarques (justifiées !) et le temps que tu m'as consacré. Je suis bluffé et impressionné, j'accepte tous tes changements sans restriction..., particulièrement la nouvelle formule en i2 qui m'évite la corvée de saisir les noms des vins à chaque ligne..., la clarté d'affichage du prix (je ne sais pas faire, et si tu me l'expliques, je le reproduirai dans mes autres fichiers), etc... J'ai gardé dans l'onglet "Paramètres" la colonne B avec ta formule en B2 : =SI(A2>0;LIGNE()-1;""), qui me permets dans l'onglet "Achats" d'utiliser en colonne B une liste déroulante avec la formule "DECALER" pour éviter les blancs en fin de liste.

Petit bémol, j'ai testé avec Bellissima (encore lui...), onglet "Dégusté le" en B73 et B165, le décompte se fait bien dans "Achats" nombre de bouteilles consommées et restant en cave, mais à la fermeture du fichier, message d'alerte (lien n°2) et création d'une nouvelle colonne D dans "Paramètres", et il reste présent dans la liste déroulante onglet "Dégusté le" ??? voir le fichier via le lien n°1.

Si tu peux me résoudre cela, c'est juste parfait, je jette mon fichier (il sera au paradis...) et le remplace par le tien sans hésiter.

Tu es ET "Ecxellent" et poète, j'adore, c'est cool.

Bonne journée et merci encore ! Dans l'attente de te lire, si si je lis tout ce que tu envoies plusieurs fois !

https://www.cjoint.com/c/MKBkGaEFluv

https://www.cjoint.com/c/MKBkHaBAAdv

Re, ne tiens pas compte du 2ème lien, j'ai trouvé la solution. Il reste juste le problème qu'il crée une nouvelle colonne dans "Paramètres" (qui actualise bien les vins dont le stock est à 0), et le vin dont le stock est à 0 reste présent dans la liste déroulante parce que la Source de la Validation des données reste bien Paramètres!$F$2:$F$11, logique.

Bonjour Ronan,

à propos du 2ème lien, c'est parfait que tu aies trouvé la solution :
ça m'évite de te décrire la manip.  ;)

lien fichier : https://www.cjoint.com/c/MKBwukKUQQ0

--------------------------------------------------------------------------------------------------------------

à l'ouverture du classeur, tu es sur la 5ème feuille "Paramètres".

oh ! mais pourquoi y'a pas tes nombres de la colonne B ?  ;)

tu as écrit : « ... qui me permets dans l'onglet "Achats" d'utiliser en colonne B
une liste déroulante avec la formule "DECALER" pour éviter les blancs en
fin de liste. »

effectivement, c'est juste ; je n'avais pas vu ça ; mais dans c'cas, y'a bien
plus simple ! avec cette formule de validation des données :

=DECALER(Paramètres!$A$2;0;0;NBVAL(Paramètres!$A:$A)-1)

et hop ! plus besoin de ta colonne de nombres !  :)

tu peux aller vérifier sur la feuille "Achats" qu'en fin de liste des cellules
de la colonne B, il n'y a pas de blancs.  :)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

sur la feuille "Paramètres", j'ai vu que tu as ajouté des lieux ; y'a des
bouteilles de vin partout, chez toi ! le Capitaine Haddock va être ravi !

je pensais que cette liste ne changerait plus ➯ feuille "Achats", colonne K,
j'avais mis pour la Validation des données cette formule courte :

=Paramètres!$F$2:$F$11

mais vu que tu ajoutes des lieux, j'ai changé pour cette formule :

=DECALER(Paramètres!$F$2;0;0;NBVAL(Paramètres!$F:$F)-1)

tiens ! elle a comme un air de ressemblance avec la formule de validation
pour les listes d'appellation, tu trouves pas ?  ;)

bon, je te rassure : tu ne vois pas double (enfin, pas encore) ➯ tu peux
continuer à trinquer avec Archibald.  ;)

à tout hasard, t'aurais pas mis des bouteilles de vins sous ton lit ou dans
l'armoire du salon ? si oui, il faut ajouter aussi "Lit" et "Salon" dans ta
liste ; tu peux même ajouter "Saloon" si tu as envie ; tu auras p't'être
la visite d'Erasmus Mulligan ?  ;)

--------------------------------------------------------------------------------------------------------------

pour la clarté d'affichage des nombres (prix ou autres) :

il faut un alignement horizontal à droite (ça tu sais faire), et il faut aussi
un retrait (appelé aussi indentation) ; voir cette image.

pour les textes : tu peux choisir alignement à gauche et retrait 1.

avec un nombre plus grand, le retrait est plus grand ; attention :
c'est un nombre entier ; nombre fractionnaire interdit !

--------------------------------------------------------------------------------------------------------------

voici un p'tit jeu : va sur la feuille "Achats" ; sélectionne par exemple A4 ; y'a :
"Lyseras 2017" ; saisis quelques espaces à gauche et à droite ; exemple :
"     Lyseras 2017     " ; appuie sur Entrée.  ;)

j'ai beaucoup hésité à le faire, car il ne faudrait pas que ça t'incite à être moins
rigoureux lors de ta saisie de données.  ;)

attention : j'ai mis ça seulement pour la colonne A, afin de t'éviter le genre de
mésaventure de Bellissima ; j'entends d'ici Agecanonix : Alésia ? connaît pas,
Alésia ! par Toutatis, Belenos, et Bellissima !

rhodo

@Ronan

va sur la feuille "Tarif" ; oh ! mais pourquoi y'a plus qu'un seul tableau ?  ;)

t'inquiètes pas : c'est le bon tableau : celui qui était à droite, donc celui
où tu saisis les données.  :)  c'est le tableau de gauche qui est passé
à la trappe (celui où y'avait toutes les formules) ; note que la colonne
"ORDRE" de l'ex-tableau droit (feue colonne J) a aussi disparu, corps
et biens (paix à son âme).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

tu peux voir que c'est bien classé par ordre chronologique des dates ;
donc c'est à partir du 1er mai 2019 que tu as commencé à picoler ;
ça fait quand même plus de 4 ans de beuveries, hein ?  ;)

si c'est pas indiscret, c'est quoi, la raison ? que s'est-il passé au juste,
ce fameux jour du 01/05/2019 ?  ^^

ah, j'ai trouvé !  :)  c'est quand tu as découvert que le 1er mai est un
jour férié, et que jusqu'en mai 2019, tu te levais trop tôt pour rien
pour aller bosser !  :(

bon, trêve de plaisanteries : elle te manque, ta liste triée dans l'ordre
des noms de vins ? t'as qu'à d'mander : fais Ctrl n.

tu veux revoir ta liste triée par dates ? fais Ctrl d.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

sur la feuille "Paramètres", tu peux au choix cliquer sur ton bouton
"Tri Alphabétique" ou faire Ctrl a : ça fera pareil !  :)

a est pour : tri des Appellations.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

la suite plus tard.  ;)

rhodo

Bonjour Rhodo, 

Je m'incline..., tu es trop fort ! Ton fichier est exactement comme je l'imaginais, franchement, chapeau bas et un immense merci...

Il me reste à arranger l'onglet Plan de cave, qui représente une cave à vin pour savoir où trouver mes bouteilles. J'ai rajouté une MFC pour vérifier le comptage des bouteilles. Quand j'ai dégusté une bouteille, elle est bien décomptée de l'onglet achat dans "bouteilles consommées", je l'enlève ensuite "physiquement" , et si dans "Plan de cave" Z2 est différent de Y24, le nombre s'affiche en couleur Rouge. Mais ce sera dans mes cordes, tu as vraiment fait l'essentiel !!!

Pour répondre à ta question, il ne s'est rien passé de particulier le 1er mai 2019, juste que j'utilisais Excel de façon basique, et que je me suis pris au jeu de faire évoluer mes tableaux avec des formules un peu plus avancées. Il me manque ton savoir pour aller plus loin avec l'utilisation du vba qui me semble inaccessible...

Et en ce qui te concerne, quelle formation as tu pour être aussi doué ?

Toujours aussi impatient de te lire, bonne jouréne !

@Ronan

je n'ai pas fait de formation particulière ; simplement, je suis passionné par Excel
et VBA, et j'ai appris en autodidacte ; ça vient avec le temps : c'est en forgeant
qu'on devient forgeron.  ;)

le fichier est terminé, mais il manque une très grosse partie concernant la liste
des vins disponibles sur la feuille Paramètres ; il y a beaucoup de choses
qui sont essentielles à comprendre (utilisation et mécanismes) ; ce sera
dans mon prochain post.

mais ça va être très long à faire ; alors peut-être ce soir ou demain.

rhodo

Tu n'en as que plus de mérite..., bravo.

Je lirai avec beaucoup d'attention ton prochain post, même si le fichier est terminé, j'aime aussi comprendre. Si tu as un conseil pour apprendre le vba, je suis à l'écoute.

Merci pour tes compétences, mais aussi ta réactivité, ta pédagogie, ton humour..., j'arrête tes chevilles vont enfler !

@Ronan

j'ai fini plus tôt que prévu.  :)  merci pour ton retour.  :)

pour apprendre le VBA, regarde ici : lien1 ; lien2

--------------------------------------------------------------------------------------------------------------

feuille "Paramètres" (partie 2)

en colonne D, il y a donc la liste des vins disponibles ; il n'y a aucune formule,
c'est uniquement des données ; tout le problème sera de la mettre à jour,
et surtout, plus exactement, aux bons moments.

dans le code VBA de Module1, c'est la sub LVD() qui écrit la liste ; mais c'est
inutile de détailler le code pour les explications.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

voici quand même le nouveau code VBA :

Sub LVD(b%) 'Liste des Vins Disponibles
  Dim T, m&, n&: m = Rows.Count
  With F01
    n = .Cells(m, 1).End(3).Row: If n = 1 Then Exit Sub
    T = .Range("A2:J" & n): n = n - 1
  End With
  Dim d&, r&, i&: Application.ScreenUpdating = 0
  With F05
    d = .Cells(m, 4).End(3).Row: r = 2
    If d > 1 Then .Range("D2:D" & d).ClearContents
    For i = 1 To n
      If T(i, 10) > 0 Then .Cells(r, 4) = T(i, 1): r = r + 1
    Next i
    .Range("D2:D" & r - 1).Sort .[D2], 1
  End With
End Sub

tu remarqueras que je n'utilise plus le nom des feuilles mais le CodeName ;
car bizarrement, avec Worksheets("Paramètres"), ça causait un plantage
alors que d'habitude, ça ne pose pas de problème.

F01 : feuille "Achats" ; F05 : feuille "Paramètres".

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

mais revenons à nos moutons.  ;)  dans ThisWorkbook, il y a :

Private Sub Workbook_Open()
  LVD 0
End Sub

ainsi, dès l'ouverture du classeur, c'est cette sub qui appelle LVD() pour
écrire la liste ; mais ensuite ? quand donc est-il nécessaire de mettre
la liste à jour ?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

d'abord sur la feuille "Achats", quand tu saisis ou changes un nombre
en colonne G "NOMBRE DE BOUTEILLES ACHETÉES" ; d'où ce code
VBA du module de la feuille "Achats" :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim col%
  With Target
    If .CountLarge > 1 Then Exit Sub
    If .Row = 1 Then Exit Sub
    col = .Column
    If col = 1 Then
      Application.EnableEvents = 0: .Value = Trim$(.Value)
      Application.EnableEvents = -1: Exit Sub
    End If
  End With
  If col = 7 Then LVD 0
End Sub

le bloc « If col = 1 Then .. End If » ne concerne pas la liste : c'est ce qui
enlève les espaces en trop dans le nom saisi en colonne A.

bien sûr le nom doit être écrit correctement pour que la mise à jour
fonctionne correctement ; sinon, c'était le cas du "Bellissima " ; ou
ça aurait été le cas pour "Naudé 23" au lieu de "Naudé-23".

j'ai préféré éviter de faire une mise à jour de la liste en cas de
changement du nom car c'est une liste des vins disponibles,
donc c'est dépendant de la quantité restant en stock.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

au cas où tu saisis un nom erroné comme "Naudé 23" : commence par
le remplacer par "Naudé-23" ; va sur la cellule G de la même ligne.

a) si la cellule est vide, saisis un nombre ; b) s'il y a déjà un nombre correct,
appuie sur les touches F2 puis Entrée : c'est comme si tu re-saisis le même
nombre ; c) si le nombre est incorrect, remplace-le par le bon nombre.

c'est a) ou b) ou c) qui va mettre à jour la liste ; sinon, ça se fera lors de la
prochaine ouverture du classeur, via la sub Workbook_Open().

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour la colonne i "NOMBRE DE BOUTEILLES CONSOMMÉES" :

si tu saisissais ou modifiais le nombre, c'est à ce moment que j'aurais fait
aussi une mise à jour de la liste ; mais comme il y a une formule ça n'est
pas possible ; c'est pourquoi la suite est sur la feuille "Dégusté_le".

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

en colonne B "Millésime", il y a une liste déroulante qui doit montrer unique-
ment les vins disponibles ; donc pour cela, j'ai choisi de faire une mise à jour
de la liste des vins de la feuille "Paramètres" quand tu saisis ou modifies une
date en colonne A ; c'est donc une liste avant retrait des bouteilles dégustées.

bien sûr, après saisie ou modification d'un nombre en colonne C "Nombre",
il faut aussi mettre à jour la liste des vins disponibles.

les 2 paragraphes qui précèdent font que voici le code VBA du module de
la feuille "Dégusté_le" :

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .CountLarge > 1 Then Exit Sub
    If .Row = 1 Then Exit Sub
    If .Column = 1 Or .Column = 3 Then LVD 0
  End With
End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

voilà pourquoi je tenais vraiment à t'en parler ; pour ce qui est de l'utilisation,
mais aussi pour que tu saches que ce n'est pas seulement dans Module1
qu'il y a du code VBA ; il y en a aussi dans ThisWorkbook et le module
des 2 feuilles "Achats" et "Dégusté_le".

--------------------------------------------------------------------------------------------------------------

pour tous les tris, le code VBA correspondant est uniquement dans
Module1 ; c'est tout ceci :

Option Explicit

Private Sub Tri(sh As Worksheet, plg$, cel$)
  Dim d&: Application.ScreenUpdating = 0
  With sh
    d = .Cells(Rows.Count, 1).End(3).Row
    If d > 2 Then
      If sh.CodeName = "F04" Then F04.Unprotect
      .Range(plg & d).Sort .Range(cel), 1
      If sh.CodeName = "F04" Then F04.Protect
    End If
  End With
End Sub

Sub TTN()
  If ActiveSheet.CodeName = "F04" Then Tri F04, "A2:D", "B2"
End Sub

Sub TTD()
  If ActiveSheet.CodeName = "F04" Then Tri F04, "A2:D", "A2"
End Sub

Sub TAP()
  If ActiveSheet.CodeName = "F05" Then Tri F05, "A2:A", "A2"
End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

attention : si un jour tu vas protéger la feuille "Tarif" avec un mot de passe,
par exemple "loup", il faudra que tu l'ajoutes sur 2 lignes de code VBA :

If sh.CodeName = "F04" Then F04.Unprotect "loup"

If sh.CodeName = "F04" Then F04.Protect "loup"

si tu oublieras de le faire, ça va planter !  :(

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

TTN : Tri Tarifs par Noms (en feuille Tarif)
TTD : Tri Tarifs par Dates (en feuille Tarif)
TAP : Tri des APpellations (en feuille Paramètres)

ces 3 subs appellent la sub Tri() avec différents paramètres,
puis c'est la sub Tri() qui fait tout le boulot réel du tri.  ;)

F04 : CodeName de la feuille "Tarif"
F05 : CodeName de la feuille "Paramètres"

--------------------------------------------------------------------------------------------------------------

si tu as des questions, n'hésite pas.  :)

rhodo

Merci pour les liens, je pense que je vais me pencher dessus, ça me parait compliqué, mais intéressant et tellement génial !

J'ai lu attentivement ton post, c'est à la fois complexe, mais très clair, je serai rigoureux dans mes saisies, c'est promis.

Il est possible que le plantage avec Worksheets soit lié au fait que je suis sur Mac ? En tout cas, plus de plantage, tout fonctionne nickel, c'est vraiment top !!!

J'ai revu presque tous mes fichiers avec le retrait des nombres, c'est beaucoup plus clair, merci aussi.

Il est fort possible que je revienne vers toi pour d'autres fichiers, ce sera toujours plaisant d'échanger en tout cas. Bonne soirée à toi, 

Amicalement