Microsoft 365 Plusieurs boutons VBA complexes dans une feuille excel

[BOILEAU]

Bonsoir,
Bonne année 2023.
Je me permets de vous contacter pour faire appel à vos compétences.
J'ai entrepris de créer un tableau Excel d'imports de lignes de facturations afin de pouvoir par la suite automatiser ma saisie comptable.
Je dois dans ce tableau créer plusieurs bouton ayant chacun des formules VBA différentes.
Cela fait 2 mois que je suis sur le sujet et je craque !
Peut-être que ce que je souhaite faire est impossible ou trop compliqué ou rendra le fichier Excel trop lourd et donc inutilisable !
Votre expertise et votre avis seront les bien venus.
Dans le fichier joint "Recette presta facture xlsm", j'ai indiqué par des notes dans chaque cellule le résultat souhaité de la formule VBA.
Les colonnes en vert turquoise sont les informations importées de mon logiciel de réservation.
J'ai limité le nombre de lignes pour l'exemple.
Je vous joint également le fichier original "Fichier original importé" de l'export de mon logiciel de réservation
J'espère ne pas vous embêter de trop.
Merci par avance de votre aide et de vos conseils.
Frédéric

 

[sylvanu]

Bonsoir Boileau,
Pas sur d'avoir tout compris, pourquoi du VBA, ne peux t on pas le faire en formules ? mais bon ... un essai en PJ avec des formules :

En I3 :
=SIERREUR(INDEX('CODES PRODUITS'!A:A;EQUIV(J3;'CODES PRODUITS'!B:B;0));"")
En K3, Idem mais lecture en colonne C :
=SIERREUR(INDEX('CODES PRODUITS'!C:C;EQUIV(J3;'CODES PRODUITS'!B:B;0));"")

Pour Colonne L , en L8 :

=SIERREUR(CNUM(STXT(J8;3+CHERCHE(" - ";J8);(CHERCHE("Nuit";J8))-(3+CHERCHE(" - ";J8))));"?")

Mais cela suppose exactement la même structure partout, en particulier ce code :

" - N Nuit"

d'où on extrait N.

 

[BOILEAU]

Bonjour Sylvanu,
Merci pour votre retour.
Une formule par cellule est très complexe à gérer. Le nombre de lignes de mes tableaux d'export n'est jamais le même et de ce fait la fonction d'une cellule ne se retrouvera pas forcément toujours à la même place. Travailler avec des formules m'obligeraient à copier/coller les fonctions dans chacune d'entre elles. Vu que dans mes colonnes I_K_L, les cellules n'ont pas toutes le même type de calcul (la même fonction), le risque d'erreur est très important.
C'est pourquoi, je préfèrerai pour la feuille, différentes formules VBA que je peux déclencher.
Peut-être que ce que je souhaite n'est pas réalisable !
Merci de votre aide.
Frédéric

 

[sylvanu]

Bonjour,

Peut-être que ce que je souhaite n'est pas réalisable !

Si ça l'est. Mais comment déclencher la mise à jour ?
En PJ un essai.
Un clic sur une cellule des colonnes I K ou L déclenche automatiquement le calcul de la bonne formule et y met la valeur.
Sinon définissez une façon de faire la mise à jour. Ce sera simple à implanter.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Fin
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [I2:I100]) Is Nothing Then
        Valeur = """" & Cells(Target.Row, "J") & """"
        f = "=SIERREUR(INDEX('CODES PRODUITS'!$A:$A;EQUIV(" & Valeur & ";'CODES PRODUITS'!$B:$B;0));"""")"
        Range(Target.Address).FormulaLocal = f
        Range(Target.Address) = Range(Target.Address).Value ' A supprimer si on veut garder la formule
    End If
    If Not Intersect(Target, [K2:K100]) Is Nothing Then
        Valeur = """" & Cells(Target.Row, "J") & """"
        f = "=SIERREUR(INDEX('CODES PRODUITS'!$C:$C;EQUIV(" & Valeur & ";'CODES PRODUITS'!$B:$B;0));"""")"
        Range(Target.Address).FormulaLocal = f
        Range(Target.Address) = Range(Target.Address).Value ' A supprimer si on veut garder la formule
    End If
    If Not Intersect(Target, [L2:L100]) Is Nothing Then
        Texte = Cells(Target.Row, "J")
        If Texte Like "*Nuit*" Then                         ' Si la phrase contient le mot magique
            Target = Split(Split(Texte, " Nuit")(0), " - ")(1)  ' On extrait le nombre de nuits
        End If
    End If
Fin:
End Sub

 

[BOILEAU]

Bravo !!!!!
Merci beaucoup.
Je vais créer un bouton qui déclenchera cette formule BVA.
Sauf erreur de ma part et sans vouloir exagérer, je crois qu'il manque la formule pour les cellules exemples I8_I14_I16_I19_I22 et pour les cellules en mauve de la colonne B.
Merci pour votre aide.
Frédéric

 

[sylvanu]

Sauf erreur de ma part et sans vouloir exagérer, je crois qu'il manque la formule pour les cellules exemples I8_I14_I16_I19_I22 et pour les cellules en mauve de la colonne B.

Euh et que doit on mettre dans les cellules en mauve et les cellules en vert ?

 

[BOILEAU]

Euh et que doit on mettre dans les cellules en mauve et les cellules en vert ?

Une note explicative est normalement présente dans chaque cellule. Sauf oubli de ma part.
Pour exemple cellule I8 = "Si une cellule de la colonne J contient un n° de résa à 6 chiffres et le mot SPAcieuse; alors la cellule colonne I prend la valeur de la cellule de la feuille code produit correspondant. Soit dans cet exemple 706137". C'est d'ailleurs sur cette formule que je me casse les dents depuis plusieurs semaine !!!!!
Pour exemple cellule B2 = " affecte un numéro qui est égal à : les 4 premières lettres du nom suivi de 0001 "

Un grand merci d'avance.

Frédéric

 

[sylvanu]

Oups, pas vu. Sorry.
Mettre des spécifications en commentaire, ce n'est pas inné pour un contributeur d'y penser.
Je vous fait ça avec un bouton.

 

[sylvanu]

Re,

C'est d'ailleurs sur cette formule que je me casse les dents depuis plusieurs semaine !!!!!

D'accord avec vous, c'est le gros morceau.
Un essai en PJ, ça donne des résultats, à vérifier.
Le bouton Orage ne sert à rien, il efface les colonnes BIKL pour faire les test.
La macro est lancée par le bouton Mise à jour.
Reste un souci : la chambre "L'intime"
L'apostrophe est un caractère reconnut par le VBA comme étant du commentaire et il ne traite pas cette chambre correctement.
Dans cette PJ j'ai triché en renommant cette chambre "L intime", et ça marche.
YaPuKa trouver une méthode pour pallier à ce problème. J'essaierais de regarder cette AM.
Bon courage.

 

[BOILEAU]

Re,

D'accord avec vous, c'est le gros morceau.
Un essai en PJ, ça donne des résultats, à vérifier.
Le bouton Orage ne sert à rien, il efface les colonnes BIKL pour faire les test.
La macro est lancée par le bouton Mise à jour.
Reste un souci : la chambre "L'intime"
L'apostrophe est un caractère reconnut par le VBA comme étant du commentaire et il ne traite pas cette chambre correctement.
Dans cette PJ j'ai triché en renommant cette chambre "L intime", et ça marche.
YaPuKa trouver une méthode pour pallier à ce problème. J'essaierais de regarder cette AM.
Bon courage.

Re,
Merci pour ce travail. Je savais que les cellules vertes de la colonne I seraient un casse tête.
Je viens de tester le fichier. Malheureusement je suis face au même problème que lors de mes propres écritures de formules.
Bien que votre formule soit beaucoup plus simple et plus rapide que ce que je pourrai écrire, il existe néanmoins un petit soucis.
Je n'ai pas du être suffisamment clair dans mes explications pour les cellules vertes de la colonne I.
Nous avons 4 chambres : La Discrète, L'Intime, La Généreuse et la SPAcieuse.
Chacune de ces chambres à un code produit qui diffère selon que ce soit une réservation faite depuis notre site internet ou depuis Booking.com ou depuis Expedia. Soit pour chacune des chambres 3 codes produits différents.
La seule façon d'identifier la provenance est le n° de la réservation se trouvant dans la ligne de la colonne J (couleur marron dans mon tableau) :
> Numéro à 6 caractères (lettres et chiffres) = Réservation depuis site internet
> Numéro à 10 caractères (lettres et chiffres) = réservation depuis EXPEDIA
> Numéro 10 + 10 caractères (lettres, signe et chiffres) = réservation Booking.com
Dans le fichier reçu,
> la valeur de la cellule I8 devrait être 706137
> la valeur de la cellule I14 devrait être 706113
> la valeur de la cellule I16 est bonne mais c'est une chance ! (réservation provenant de Booking)
> la valeur de la cellule I19 devrait être 706107
> la valeur de la cellule I22 est bonne mais c'est une chance ! (réservation provenant de EXPEDIA)

C'est pour cette complexité que je me dis, que peut-être, cela n'est pas possible !
J'espère avoir été plus clair dans mon explication.

je suis sincèrement désolé et surtout je comprendrai si cela est trop lourd que vous abandonniez.

Merci pour tout

Frédéric

 

[sylvanu]

C'est ce qu'est censé faire le code à partir de la ligne 24.
Recherchant l' Agence, et la Chambre, puis la ligne qui correspond avec ces deux termes au code.
Cherchez de ce coté. J'essaierais de regarder de mon coté.

 

[sylvanu]

Prenez cette PJ c'est plus parlant pour localiser les erreurs. J'ai remplacé les codes par des "codes parlant"

J'ai bien une détection platforme et chambre. Mais pet être que les codes remontés sont faux.
( J'ai considéré que "une réservation faite depuis notre site en direct" c'était AIRBNB, sinon il manque une codification pour réséerv airbnb )

 

[BOILEAU]

Prenez cette PJ c'est plus parlant pour localiser les erreurs. J'ai remplacé les codes par des "codes parlant"

J'ai bien une détection platforme et chambre. Mais pet être que les codes remontés sont faux.
( J'ai considéré que "une réservation faite depuis notre site en direct" c'était AIRBNB, sinon il manque une codification pour réséerv airbnb )

Oup's !
Je viens de me rendre compte que l'erreur vient de moi. J'ai complètement oublié de supprimer les lignes AIRBNB 127, 128 129 130 de la feuille "Codes Produits". Elles n'existent plus.
Toutes mes excuses.
Si l'apostrophe de L'Intime est un soucis non résolvable, je peux essayer de le supprimer dans ma fiche d'hôtel. Cela fera une faute d'orthographe, mais bon...

Pensez-vous qu'il serait possible de rajouter dans la formule la fonction suivante :
Exemple:
> Si cellule B2 n'est pas vide, alors cellules K2 et L2 sont vides et
Colonne K > si cellule vide, alors la fonction est "M8/L8" et
Colonne L > si cellule vide, alors la fonction est "M3/K3"
Cet ajout me permettrait d'éviter de remplir manuellement toutes les cellules concernées par une fonction de division.

Encore toutes mes excuses et un grand merci.

Frédéric

 

[sylvanu]

Bonsoir,
Un nouvel essai en PJ.
Ca a demandé pas mal de sécurité à cause des divisions, donc le code est assez long pour si peu.
Attention ligne c'est "Vin de noix - 6 cl" et non "Vins de noix - 6 cl"
Attention, ce genre de faute ne pardonne pas car on perd la corrélation produit vs base de données.

Reste l'apostrophe.

Cela fera une faute d'orthographe, mais bon...

Une faute d'orthographe pour quelques lignes de code, ce serait pathétique.

 

[BOILEAU]

Bonsoir,
Un nouvel essai en PJ.
Ca a demandé pas mal de sécurité à cause des divisions, donc le code est assez long pour si peu.
Attention ligne c'est "Vin de noix - 6 cl" et non "Vins de noix - 6 cl"
Attention, ce genre de faute ne pardonne pas car on perd la corrélation produit vs base de données.

Reste l'apostrophe.

Une faute d'orthographe pour quelques lignes de code, ce serait pathétique.

Que dire de plus !!!

Un grand merci
Il reste la notion AIRBNB pour le code produit c'est normalement le code à 6 chiffres (réservation direct).
Je vous joins votre fichier dans lequel j'ai enlevé le produit AIRBNB, corrigé la taxe de séjour et suppression de l'apostrophe de L Intime.

Vous êtes un maître. En une journée, c'est très impressionnant.

Frédéric