VBA - Recherche valeur dans un autre fichier Excel
Bonjour à toutes et à tous, je suis nouveau sur ce forum, je vous ai rejoins car j'ai un petit problème pour la mise en place d'une macro.
Je m'explique, pour le moment j'ai deux fichiers excel (que je vous joins). Le premier fichier nommé "Tarifs" possède 4 pages, chaque page renseignant sur les tarifs d'un produit.
En page 1, on trouve les tarifs du produit AFG, en page 2 les tarifs du produits P30D, etc ... en sachant que les tarifs sont associés à un département (95 choix possibles) et par tranche de poids (de 0 à 10 kilos, de 11 à 20 kilos, etc ...)
Prenons par exemple un envoi AFG de 75 kilos dans le département 23 le tarif serai de 191€/kilo soit 14325€ pour les 75 kilos.
Le second fichier excel, nommé "detail_vente", est celui concerné par la macro. Cette macro consiste dans un premier temps à inviter l'utilisateur à rentrer des données sur les ventes (le produit, le poids, la destination, le prix réellement facturé).
La dernière étape de cette macro consiste à afficher le prix qui AURAIT DÛ être facturé. Pour se faire il faut rechercher le prix unitaire dans le fichier "Tarifs" qui correspond à ce qui a été saisie par l'utilisateur et le multiplier par le poids.
Je m'explique, si lors de la saisie l'utilisateur renseigne le produit comme étant du P30D, pour un poids de 21 kilos, à destination du 59, il faut que la macro effectue la recherche dans le fichier "Tarifs", dans la feuille "P30D".
Et c'est ici que je bloque, je ne parviens pas à indiquer que ma recherche doit s'effectuer dans la feuille qui porte le nom du produit renseigné.
Je vous transmet les deux fichiers :
Et le code actuel :
Sub detail_vente()
Dim produit$, destination, poids!, client$, prix_reel%, prix_preetabli%, retour&
Dim Lign As Long
'ouverture du fichier "tarifs" s'il est fermé
On Error Resume Next
Workbooks(Tarifs).Activate
If Err <> 0 Then Workbooks.Open "C:\Users\rodrigues.neiva\PROJET ANALYSE OBJECTIFS\Tarifs.xls"
On Error GoTo 0
'on revient sur le fichier contenant la macro
ThisWorkbook.Activate
'Saisie des ventes
Range("A1").Select
Lign = 3
Do Until retour = 7
client = InputBox("Saisir le nom du client.", "Saisie des éléments de la vente.")
ActiveCell.Offset(Lign, 0) = client
produit = InputBox("Saisir le produit vendu.", "Saisie des éléments de la vente.")
ActiveCell.Offset(Lign, 1).Value = produit
destination = InputBox("Saisir la destination.", "Sasie des éléments de la vente.")
ActiveCell.Offset(Lign, 2).Value = destination
poids = InputBox("Saisir le poids.", "Saisie des éléments de la vente.")
ActiveCell.Offset(Lign, 3).Value = poids
prix_reel = InputBox("Saisir le prix de vente réel.", "Saisie des éléments de la vente.")
ActiveCell.Offset(Lign, 4).Value = prix_reel
Dim L As Integer, C, Col As Integer
With Workbooks(Tarifs.xls).Sheets(produit)
L = .Columns(1).Cells.Find("FR-" & destination)
For Each C In .Columns
If .Cells(3, C.Column) < poids And .Cells(3, C.Column + 1) > poids Then
Col = C.Column: Exit For
End If
Next C
ActiveCell.Offset(Lign, 4).Value = poids * .Cells(L, Col)
End With
retour = MsgBox("Y a-t-il d'autres ventes à saisir ?", vbYesNo, "Saisie des éléments de la vente.")
Lign = Lign + 1
Loop
End SubLe problème intervient à partir de cette ligne :
With Workbooks(Tarifs.xls).Sheets(produit)Voilà mon problème
J'espère avoir été le plus clair possible, si jamais certains points doivent être éclaircis n'hésitez pas à me le dire.
Bien sûr je suis ouvert à toutes modifications du code ou des fichiers, qui pourrai me faciliter la tâche.
En vous remerciant par avance.
Cordialement
maiito
Bonjour et bienvenue sur le forum
Ci-joint ta macro avec quelques retouches.
Mais ne vaudrait-il pas mieux passer par une boîte de dialogue plutôt que de multiplier les inputbox ?
A toi de voir...
Bye !
Sub detail_vente()
Dim produit$, destination, poids!, client$, prix_reel%, prix_preetabli%, retour&
Dim Lign As Long
'Initialiser la zone où on va écrire les résultats
Range("A3").CurrentRegion.Offset(1, 0).ClearContents
'ouverture du fichier "tarifs" s'il est fermé
On Error Resume Next
Workbooks(Tarifs).Activate
If Err <> 0 Then Workbooks.Open "C:\Users\rodrigues.neiva\PROJET ANALYSE OBJECTIFS\Tarifs.xls"
On Error GoTo 0
'on revient sur le fichier contenant la macro
ThisWorkbook.Activate
'Saisie des ventes
Range("A1").Select
Lign = 3
Do Until retour = 7
client = InputBox("Saisir le nom du client.", "Saisie des éléments de la vente.")
If client = "" Then End
ActiveCell.Offset(Lign, 0) = client
produit = InputBox("Saisir le produit vendu.", "Saisie des éléments de la vente.")
ActiveCell.Offset(Lign, 1).Value = produit
destination = InputBox("Saisir la destination.", "Sasie des éléments de la vente.")
ActiveCell.Offset(Lign, 2).Value = destination
poids = InputBox("Saisir le poids.", "Saisie des éléments de la vente.")
ActiveCell.Offset(Lign, 3).Value = poids
prix_reel = InputBox("Saisir le prix de vente réel.", "Saisie des éléments de la vente.")
ActiveCell.Offset(Lign, 4).Value = prix_reel
Dim L As Integer, C, Col As Integer
With Workbooks("tarifs.xls").Sheets(produit)
L = .Columns(1).Find("FR-" & destination).Row
For Each C In .Range("B3:L3")
If C < poids And C.Offset(0, 1) > poids Then
Col = C.Column: Exit For
End If
Next C
ActiveCell.Offset(Lign, 5).Value = poids * .Cells(L, Col)
End With
retour = MsgBox("Y a-t-il d'autres ventes à saisir ?", vbYesNo, "Saisie des éléments de la vente.")
Lign = Lign + 1
Loop
End SubMerci beaucoup pour ta réponse gmb, j'ai juste une petite remarque, la recherche s'effectue, mais après plusieurs essais je me rend compte que le résultat retourné n'est pas le bon.
Je m'explique, lorsque je rentre mes critères, la macro me sort un prix pré-établi de 1200€par exemple, mais lorsque je vérifie manuellement, pour être sûr que tout fonctionne parfaitement, je me rend compte que le résultat est erroné.
Prenons par exemple en destination le 45 et un poids de 12 kilos, la macro me renvoie un prix de 1668€
Lorsque je vérifie moi même, je regarde le prix d'un envoi dans le 45, dans la tranche 10 à 20 kilos (cellule C48 dans le fichier que j'ai joins précédemment) et je trouve en prix unitaire de 83€ ce qui nous fait 996€ pour les 12 kilos.
J'ai bien regardé le code pour essayer de trouver d'où peut provenir cette erreur, mais sans connaître le fonctionnement du code permettant la recherche je ne parviens pas à y remédier.
Ensuite, qu'entends tu pars une boîte de dialogue ? Une seule boîte qui s'ouvre dans laquelle l'utilisateur doit renseigner les différents critères à la place d'une fenêtre par critère ?
Je suis ouvert à toutes propositions qui permettrai de simplifier l'utilisation de la macro, et d'améliorer l'ergonomie de celle-ci.
S'il faut, je peux également apporter quelques retouches au fichier Tarifs, si cela permet de faciliter la mise en place de la macro.
Merci par avance, je continu à chercher de mon côté en attendant une réponse.
Cordialement
maiito
Bonjour
Dans l'exemple que tu donnes, tu oublies de dire de quel produit il s'agit.
En fait, la macro donne le résultat du produit AFG et tu as fais le calcul à la main pour un produit EXPD.
Dans le premier cas, le prix unitaire est de 139 € soit pour 12 kg un montant de 1668 €.
Je n'ai pas gardé la macro originale adaptée avec les retouches que je tai adressées.
Il y avait peut-être un bug qui faisait que elle prenait toujours le produit AFG.
Mais comme tu écris :
Je suis ouvert à toutes propositions qui permettrai de simplifier l'utilisation de la macro, et d'améliorer l'ergonomie de celle-ci.
je t'ai préparé cette nouvelle version.
Dis-moi ce que tu en penses et surtout si les calculs sont exacts.
A te relire.
Bye !
Bonjour, désolé de revenir si tard vers toi, étant en alternance, je n'ai accès aux fichiers excel uniquement quand je suis en entreprise ^^
Je viens de regarder la version que tu as mis au point. Pour l'erreur de calcul j'ai bien compris, et le problème ne se pose plus. Je t'en remercie.
En revanche, tu as inséré un nouveau bouton, mais la macro associée ne contient que ces lignes, qui étaient déjà présentent dans la première macro :
Sub Vente()
'Initialisation du tableau de résultats
Range("A3").CurrentRegion.Offset(1, 0).ClearContents
UserForm1.Show
End SubEnsuite je reviens sur un point que tu avais abordé, une boîte de dialogue, pour éviter d’enchaîner les InputBox. Peut être voulais tu mettre ce système en place dans la macro associée au nouveau bouton car je n'ai vu aucun changement.
Merci encore pour ton aide.
EDIT : je viens de comprendre la ligne "UserForm1.Show" qui renvoie à la boite de dialogue que tu as créé, et qui est en effet beaucoup plus sympathique que l'enchaînement des InputBox. En revanche cette boîte de dialogue ne se "lance" pas ^^'
Bonjour
maiito a écrit :cette boîte de dialogue ne se "lance" pas ^^'
Pour qu'elle se lance, il faut que le fichier des tarifs soit ouvert.
J'ai ajouté un message d'alerte pour le rappeler, le cas échéant.
Cela marche-t-il ?
Bye !
Ah oui d'accord, ta macro fonctionne parfaitement bien, aucune erreur de calcul, la saisie des informations et beaucoup plus agréable que ce que j'avais initialement mis en place et la boîte de dialogue s'ouvre correctement.
J'ai juste apporté une petite modification, au lieu d'afficher un message pour informer que le classeur Tarifs n'est pas ouvert, j'ai "automatiser" son ouverture, comme ça plus de problème.
Je tenais à te remercier grandement, tu m'as beaucoup aidé. Je vais continué sur ce projet et je reviens sur le forum si jamais je rencontre un quelconque soucis.
Encore merci.
maiito
Me revoilà ! Je ne serai pas parti bien longtemps finalement ... ^^'
Dans le fichier tarifs que j'ai joins, peu importe le produit, il y a le même nombre de tranche de poids. J'avais créé ce fichier très rapidement, pour pouvoir illustrer ma demande. En revanche je ne pensais plus au fait qu'en réalité, il peut y avoir différente tranches, et en quantité différentes également.
En rajoutant d'autres tranches, je me suis rendu compte qu'il y avait une erreur dans le calcul du coût pré-établi concernant les "nouvelles" tranches.
J'ai regardé le code, je pense avoir compris que le fait que l'encadrement des tranches soit différent n'est pas un problème, mais que le problème vient plutôt du nombre de tranche.
Sub coutCalculé()
5 If ComboBox1 <> "" And ComboBox2 <> "" And TextBox2 <> "" Then
Set f = Workbooks("Tarifs.xls").Worksheets(ComboBox1.Value)
ln = f.Range("A2:A" & f.Range("A" & Rows.Count).End(xlUp).Row).Find(ComboBox2, lookat:=xlWhole).Row
For col = 2 To 13
If f.Cells(3, col) <= Val(TextBox2) And f.Cells(3, col + 1) > Val(TextBox2) Then
cout = f.Cells(ln, col) * Val(TextBox2)
Label8 = cout & " €"
End If
Next col
End If
End SubJ'ai essayé de modifier là ligne :
For col = 2 To 13de cette façon :
For col = 2 To 20J'ai mis "20" en supposant que c'est le nombre de colonnes dans lesquelles la recherche s'effectue, et en étant sur qu'il n'y aura jamais plus de 20 colonnes. Mais cela ne fonctionne pas.
Je joins les dernières versions des fichiers (avec l'ajout de différentes tranches sur le fichier Tarifs) :
Merci d'avance
maiito
Bonjour
Changer 13 par 20 n’est pas bon car la macro va prendre tous les prix entre les colonnes 2 et 20 et s’il n’y en a pas , elle va prendre un prix nul.
Il faut lui indiquer la colonne où se trouve le dernier prix.
J’ai par ailleurs un peu modifié pour tenir compte justement du dernier prix, colonne des 300 ou 1000kg selon les feuilles. Si le poids dépasse ces valeurs (ex 310 ou 1200), le calcul se fait désormais avec ce tarif.
Cela te convient-il ?
Dim f, ln, col, cout, w, flag, colMax, flag2
…
…
Sub coutCalculé()
If ComboBox1 <> "" And ComboBox2 <> "" And TextBox2 <> "" Then
Set f = Workbooks("tarifs.xls").Worksheets(ComboBox1.Value)
ln = f.Range("A2:A" & f.Range("A" & Rows.Count).End(xlUp).Row).Find(ComboBox2, lookat:=xlWhole).Row
colMax = f.Cells(3, 256).End(xlToLeft).Column
flag2 = 0
For col = 2 To colMax
If f.Cells(3, col) <= Val(TextBox2) And f.Cells(3, col + 1) > Val(TextBox2) Then
cout = f.Cells(ln, col) * Val(TextBox2)
Label8 = cout & " €"
flag2 = 1
End If
Next col
If flag2 = 0 Then
cout = f.Cells(ln, col - 1) * Val(TextBox2)
Label8 = cout & " €"
End If
End If
End SubTout fonctionne parfaitement bien maintenant ! Je t'en remercie
Si j'ai d'autres difficultés, je reviens vers toi
maiito
Me revoilà... une fois de plus ... ^^'
J'ai continué d'avancer sur cette macro, mais suite à une mise à jour des tarifs, je dois modifier quelque chose dans le code précédent, je m'explique.
Jusqu'à présent, tous les produits portés sur les 96 départements Français, sauf que dorénavant, nous avons un produit qui ne porte que sur 3 départements (59-62-80). Ce que je voudrai, c'est que lorsque l'utilisateur entre une vente concernant ce produit, la liste déroulante des destinations ne lui propose que ces 3 départements et non pas la liste entière car aucun tarif n'est rattaché aux autres destinations.
Ensuite,le produit "Pack 30" ne se met pas dans la liste déroulante des produits, et je ne comprend pas pourquoi ^^'
J'imagine qu'il faut changer quelque chose dans ces lignes :
For Each f In Workbooks("Tarifs.xls").Worksheets
If f.Range("A3").Value = "DEPT." And f.Range("B2").Value = "POIDS" Then
Me.ComboBox1.AddItem f.Name
End If
Next f
With Workbooks("Tarifs.xls").Worksheets("MessageriePlus")
ComboBox2.List = .Range("A4:A" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With Malgré plusieurs tentatives, je ne parviens pas à trouver de solution ^^'
Voici les fichiers dans leur dernière version :
Merci d'avance.
maiito
Bonjour
maiito a écrit :Ce que je voudrai, c'est que lorsque l'utilisateur entre une vente concernant ce produit, la liste déroulante des destinations ne lui propose que ces 3 départements et non pas la liste entière car aucun tarif n'est rattaché aux autres destinations.
OK
le produit "Pack 30" ne se met pas dans la liste déroulante des produits, et je ne comprend pas pourquoi
C'est parcequ'il y a un espace indésirable à la fin de ''POIDS" dans la cellule B2 feuille ''Pack 30" des Tarifs
Nouvelle version :
Te convient-elle ?
Bye !
Merci pour cette nouvelle version, en effet je n'avais pas vu l'espace à la suite de "POIDS" et même si je l'avais vu, je n'aurai pas pensé que le problème puisse venir de là pour être franc ^^
Pour la liste déroulante limitée à 3 destinations, tout est impeccable !
Par contre, en testant cette nouvelle macro, lorsque je saisie ma première vente, tout se passe bien, mais lorsque je valide pour en saisir une seconde il y a une erreur, je ne peux pas saisir d'autres ventes.
Sans trop vouloir m'avancer, je pense que le problème vient des lignes qui permettent de limiter à 3 le nombre de destinations pour le produit "Bi-quotidien", puisqu'en supprimant les lignes ci dessous, dans le Private Sub ComboBox1_Change(), la macro fonctionne mais pour le "Bi-quotidien" tous les départements réapparaissent dans la liste déroulante.
With Workbooks("Tarifs.xls").Sheets(ComboBox1.Value)
ComboBox2.Clear
ComboBox2.List = .Range("A4:A" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End WithA part ce petit problème tout va très bien, et encore merci pour ton aide.
Juste une petite question, dans l'état actuel des fichiers, si je les déplace sur un autre Pc, via une clef Usb, le chemin permettant l'ouverture du fichier Tarifs.xls ne sera plus correcte, donc la macro ne fonctionnera pas ?
maiito
Problème résolu ! Merci !
Mais, décidément il y a toujours un mais, ... je me suis aperçu que pour le calcul du coût pré-établi concernant la 1ère de tranche de prix, quelque soit le produit, je n'ai aucun retour.
J'ai essayé quelques modifications sur la partie ci dessous, notamment le dernier If, mais rien n'y fait, le tarif n'apparaît pas ou alors il n'est pas le bon.
Sub coutCalculé()
If ComboBox1 <> "" And ComboBox2 <> "" And TextBox2 <> "" Then
Set f = Workbooks("Tarifs.xls").Worksheets(ComboBox1.Value)
ln = f.Range("A2:A" & f.Range("A" & Rows.Count).End(xlUp).Row).Find(ComboBox2, lookat:=xlWhole).Row
colMax = f.Cells(3, 256).End(xlToLeft).Column
flag2 = 0
For col = 2 To colMax
If f.Cells(3, col) <= Val(TextBox2) And f.Cells(3, col + 1) > Val(TextBox2) Then
cout = f.Cells(ln, col + 1)
Label8 = cout
flag2 = 1
End If
Next col
If flag2 = 0 Then
cout = f.Cells(ln, col)
Label8 = cout
End If
End If
End SubEnsuite, juste une petite question d'ordre pratique, dans l'état actuel des fichiers, si je les déplace sur un autre Pc, via une clef Usb, le chemin permettant l'ouverture du fichier Tarifs.xls ne sera plus correcte, donc la macro ne fonctionnera pas ?
maiito
Bonjour
Nouvelle version à tester.
maiito a écrit :dans l'état actuel des fichiers, si je les déplace sur un autre Pc, via une clef Usb, le chemin permettant l'ouverture du fichier Tarifs.xls ne sera plus correcte, donc la macro ne fonctionnera pas ?
Et non !
Si tu veux que la macro fonctionne à partir d’un nouveau poste de travail, il te faut neutraliser les 2 premières instructions et ouvrir le fichier tarif en faisant ‘’Fichier – Ouvrir’’
Si tu veux installer le fichier sur ce nouveau poste, il te faut alors changer les adresses que donnent les 2 premières instructions.
Bye !
Bonjour gmb, je reviens vers toi, j'ai testé la dernière version que tu m'as envoyé, tout est parfait, et je t'en remercie.
Depuis j'ai continué d'avancer sur mon projet, notamment sur la partie "Analyse", mais je rencontre un petit problème.
Sur la feuille analyse, dans les cellules C26 et C27, j'ai mis des formules, qui me semblent correctes, mais qui ne retournent aucun résultat. J'ai d'ailleurs utilisé la même structure dans les cellules B26 et B27 et là tout fonctionne.
Le but est de faire la somme des CA en fonction des produits.
En sachant que :
Les formules sont insérées dans les cellules et non dans la macro, car je sais qu'il faut utiliser SumIf, mais je ne sais pas l'utiliser lorsqu'il y a plusieurs conditions et que les plages se trouvent sur une autre feuille du classeur. Je ne sais pas laquelle des solutions est préférable entre passer pas la macro ou pas.
De manière générale, la feuille "analyse" n'est pas gérée par la macro, à part la saisie des objectifs.
Voici les fichiers avec mes modifications :
Cordialement
maiito
Bonjour
Je pense que cela tient au fait que les prix dans la feuille « Ventes » sont écrits en texte et non en nombre.
C’est la faute à la macro qui a écrit ces valeurs : rappelle moi donc le nom de son auteur …(pas fort le mec !)
Pour corriger je te propose de modifier légèrement une instruction pour la forcer à écrire des nombres. Il suffit de multiplier par 1 la quantité concernée :
Private Sub CommandButton1_Click() 'Valider
'Tests sur les saisies
If TextBox1 = "" Or ComboBox1 = "" Or ComboBox2 = "" _
Or TextBox2 = "" Or TextBox3 = "" Then
MsgBox "Saisies incomplètes.", 16
Exit Sub
End If
ln = Range("A" & Rows.Count).End(xlUp)(2).Row
'Ecriture des données
Range("A" & ln) = TextBox1
Range("B" & ln) = ComboBox1
Range("C" & ln) = ComboBox2
Range("D" & ln) = TextBox2 & " kg"
Range("E" & ln) = TextBox3 & " €"
'Range("F" & ln) = cout & " €"
Range("F" & ln) = (cout & " €") * 1OK ?
Dis-moi le résultat.
Bye !
C'est bon ça marche ^^ J'aurais jamais pensé à ça moi donc plus fort que moi le mec ahah
Merci beaucoup en tout cas !
maiito
Nouvelle petite question :
Après proposition de cet outil à mon tuteur, celui-ci le trouve très intéressant, et bla bla bla MAIS il le trouve pas entièrement exploitable car la partie de saisie des ventes lui prendrai beaucoup trop de temps ... ce que je peux comprendre les mois où il y a plus de 200 ventes à saisir, donc plus de 200 lignes à enregistrer.
Entrons dans le vif du sujet, toutes les informations relatives aux ventes sont "stockées" dans 2 logiciels de l'entreprises (logiciels développés sur mesures pour l'activité de l'entreprise). Ma question est donc, est-il possible, à l'aide d'une macro d'aller chercher toutes ces informations, ou une partie, dans ces logiciels afin de contourner la saisie ?
En sachant que les logiciels en question, je sais les utiliser pour leur usage principal, mais je sais en aucun cas gérer l'aspect informatique pur, j'entend par là, le fait d'avoir accès à un quelconque code, de le modifier, d'en extraire une partie etc ... L'informatique est loin d'être mon domaine, même si je m'y intéresse ^^'
Voilà, je sais pas si j'ai été assez clair ou pas. Je ne demande pas la méthode, de toute façon sans le détails concernant le logiciel je me doute bien que personne ne pourra m'aider, mais juste si c'est faisable.
Merci d'avance