Discussion :

[NamName]

Bonjour le forum,

J'ai pas mal tourné sur les différents forums, mais pas moyen de trouver une solution a mon problème .

J'ai dans ma colonne A une devise, dans la colonne B un taux, dans la colonne C la date à laquelle le taux a été rentré.
Je cherche à créer un Userform, dans lequel:
* Dans la combobox1 --> il y a liste des taux (Ca pas de soucis)
* Dans La Textbox1 --> l'utilisateur saisit une date
* Dans la Textbox3 --> Affichage automatique du taux de la devise [Le taux doit être celui saisi le plus récemment, mais ne peut pas être postérieur à la date saisie]
* Dans la Textbox2 --> La date de saisie du Taux en question.

Comme les dates sont saisies dans par ordre croissant, j'étais partie sur quelque chose comme ça, mais comme vous pouvez le voir, je ne sais pas comment exprimer ma condition que le résultat trouvé ne peut etre postérieur à la date saisie.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Private Sub Textbox1_AfterUpdate()

Dim DateTaux As Date

DateTaux = Application.VLookup(ComboBox1, Range("A2:C10000"), 3, False)
If DateTaux > TextBox1.Value Then
     XXXXXXXX
Else
    TextBox2 = DateTaux
End If

End Sub

Voilà, si certains préfèrent travailler sur fichier, j'en joint un en Exemple.

Merci d'avance

[Menhir]

Les UserForm, ce n'est pas ma tasse de thé, mais je te mets en garde : les dates et les TextBox (ou tout ce qui est "text" d'une manière générale), ça ne fait pas bon ménage.
A moins de prendre de grandes précautions, c'est la cata garantie.

[NamName]

Oh .
Je vais finir par me demander s'il existe une solution du coup.

Merci quand même d'avoir regardé

[Menhir]

C'est faisable à condition de traduire la date saisie en TextBox en une vraie date dans les règles de l'art.
Il y a eu plusieurs sujets sur la transformation de Text en Date avec VBA.
https://www.developpez.net/forums/d2.../#post11113383
https://www.developpez.net/forums/d1.../#post10901853

Pour la partie UserForm, je laisse répondre des participants plus férus que moi sur le sujet.

[Thautheme]

Bonjour le fil, bonjour le forum,

Peut-être comme ça. Remplace ton code par celui-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Option Explicit 'oblige a déclarer toutes les variables
Private TV As Variant 'déclare la variable TV(Tableau des Valeurs)
 
Private Sub UserForm_Initialize() 'à l'initialisation de l'UserForm
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
 
Set O = Worksheets("Feuil1") 'définit l'onglet O
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
TV = O.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
For I = 2 To UBound(TV, 1) 'boucle sur toutes les ligne I du tableau des valeur (en partant de la seconde)
    D(TV(I, 1)) = "" 'alimente le dictionnaire D avece les données de la colonne 1 de TV
Next I 'prochaine ligne de la boucle
Me.ComboBox1.List = D.keys 'alimente la Combobox1 avec la liste des éléments du dictionnaire D sans doublon
End Sub
 
Private Sub TextBox1_AfterUpdate() 'à l'actualisation de la TextBox1
Dim DT As Long 'déclare la varialbe DT (Date de la Textbox)
Dim DL As Long 'déclare la varialbe DL (Date de la Ligne)
Dim DM As Long 'déclare la varialbe D'M (Date Maximum)
Dim I As Integer 'déclare la variable I (Incrément)
Dim T As Double 'déclare la variable T (Taux)
 
DT = CLng(DateSerial(Year(Me.TextBox1.Value), Month(Me.TextBox1.Value), Day(Me.TextBox1.Value))) 'définit la date de la TextBox DT (en entier long)
For I = 2 To UBound(TV, 1) 'boucle sur toutes les ligne I du tableau des valeurs (en partant de la seconde)
    DL = CLng(DateSerial(Year(TV(I, 3)), Month(TV(I, 3)), Day(TV(I, 3)))) 'définit la date de la ligne DL (en entier long)
    If TV(I, 1) = Me.ComboBox1.Value Then 'condition 1 si la donnée de la ligne de la boucle en colonne 1 est égale à la valeur de la ComboBox1
        If DL > DM And DM < DT Then 'condition 2 : si la date de la ligne DL est supérieure à la date max DM et si elle est inférieure à la date de la TextBox DT
            DM = DL 'la date Max DM devient la date de la ligne
            T = CDbl(TV(I, 2)) 'le taux T devient le taux de la ligne de la boucle
        End If 'fin de la condition 2
    End If 'fin de la condition 1
Next I 'prochaine ligne de la boucle
Me.TextBox2.Value = CDate(DM) 'renvoie la date DM dans la TextBox2
Me.TextBox3.Value = T 'renvoie le taux T dans la TextBox3
End Sub

[NamName]

Bonjour Thautheme,

Merci beaucoup pour cette solution qui s'approche grandement de ce que je recherche (Et pour toutes les annotations du code!)
Je vois que le résultat n'est pas toujours celui attendu, mais je vais essayer d'arranger ça, tu m'a donné les grandes lignes nécéssaires !

Simple question, je ne vois pas de définition de ce à quoi corresponds BM.
C'est moi qui ai raté la lecture d'une ligne ?
Peux-tu m'éclairer ?

Merci d'avance!

Et Merci pour les liens Menhir

[Thautheme]

Re,

Ce n'est pas BM mais DM, la Date Max qui est déclarée en ligne 21 et définie dans la ligne 30...

[Pierre Fauconnier]

Salut.

Je propose une démarche différente, qui s'appuie sur une conception de classeur correcte.

1. Un tableau structuré contenant la liste des devises ordonnées par ordre croissant (voir mon tuto sur https://fauconnier.developpez.com/tu...ux-structures/
2. Un tableau structuré contenant les cours par devise et par jour (Devise, taux, date) triés par devise puis par date croissante.

A ce stade, on peut déjà trouver le taux de la date inférieure la plus proche avec une combinaison INDEX EQUIV en matricielle


Après, sur base d'un userform contenant:

1. cboCurrency, liste déroulante alimentée par le tableau des devises;
2. tboDate, Textbox qui sera alimenté par l'utilisateur;
3. tboRate, qui sera alimenté par une fonction qui calcule le taux;
4. btnValidate, bouton qui appellera le calcul.

Le userform est initialisé par une procédure d'un module standard:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub Test()
  With UserForm2
    .cboCurrency.List = Range("t_Devises[devise]").Value
    .Show
  End With
End Sub

Dans le Userform, le code suivant qui se sert de EVALUATE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit
 
Private Sub btnValidate_Click()
  CalculateRate
End Sub
 
Sub CalculateRate()
  Dim Formula As String
 
  Formula = "=IFERROR(INDEX(t_Taux[Taux],MATCH(1,(t_Taux[Devise]=""{currency}"")*(t_Taux[Date]={date}),1)),""N/A"")"
  Formula = Replace(Replace(Formula, "{currency}", cboCurrency.Value), "{date}", CDate(tboDate.Value) * 1)
  tboRate.Value = Evaluate(Formula)
End Sub

Pour moi, reconstruire la liste des devises avec un dictionnaire ou autre révèle un problème de conception. Cette liste doit préexister à l'utilisation du formulaire et c'est sur cette liste que l'on s'appuie, quitte à vider le tableau et à la recréer. Il faudrait idéalement retrier les taux avant l'appel du userform pour que EVALUATE fonctionne. Il faudra aussi s'assurer que la valeur saisie dans tboDate est bien une date valide. Mais cela se fait en sériant les problèmes et en les divisant en petits morceaux (procédures et fonctions qui ne font qu'une chose). Idéalement, le calcul du taux devrait être déporté hors du userform, car ce n'est pas le rôle du userform d'effectuer le calcul...

Pour ce qui est de la transformation de texte saisi dans un userform en date, il n'y a rien de magique ou d'aléatoire dans la démarche.

[NamName]

Rebonjour à vous,

J'ai adapté la solution de Thautheme, j'arrive à ce résultat:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Option Explicit 'oblige a déclarer toutes les variables
Private TV As Variant 'déclare la variable TV(Tableau des Valeurs)
 
Private Sub UserForm_Initialize() 'à l'initialisation de l'UserForm
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
 
Set O = Worksheets("Feuil1") 'définit l'onglet O
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
TV = O.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
For I = 2 To UBound(TV, 1) 'boucle sur toutes les ligne I du tableau des valeur (en partant de la seconde)
    D(TV(I, 1)) = "" 'alimente le dictionnaire D avece les données de la colonne 1 de TV
Next I 'prochaine ligne de la boucle
Me.ComboBox1.List = D.keys 'alimente la Combobox1 avec la liste des éléments du dictionnaire D sans doublon
End Sub
 
Private Sub TextBox1_AfterUpdate() 'à l'actualisation de la TextBox1
Dim DT As Long 'déclare la varialbe DT (Date de la Textbox)
Dim DL As Long 'déclare la varialbe DL (Date de la Ligne)
Dim DM As Long 'déclare la varialbe D'M (Date Maximum)
Dim I As Integer 'déclare la variable I (Incrément)
Dim T As Double 'déclare la variable T (Taux)
 
DT = CLng(DateSerial(Year(Me.TextBox1.Value), Month(Me.TextBox1.Value), Day(Me.TextBox1.Value))) 'définit la date de la TextBox DT (en entier long)
For I = 2 To UBound(TV, 1) 'boucle sur toutes les ligne I du tableau des valeurs (en partant de la seconde)
    DL = CLng(DateSerial(Year(TV(I, 3)), Month(TV(I, 3)), Day(TV(I, 3)))) 'définit la date de la ligne DL (en entier long)
    If TV(I, 1) = Me.ComboBox1.Value Then 'condition 1 si la donnée de la ligne de la boucle en colonne 1 est égale à la valeur de la ComboBox1
        If DL <= DT And DL > DM Then 'condition 2 : si la date de la ligne DL est supérieure à la date max DM et si elle est inférieure à la date de la TextBox DT
            DM = DL 'la date Max DM devient la date de la ligne
            T = CDbl(TV(I, 2)) 'le taux T devient le taux de la ligne de la boucle
        End If 'fin de la condition 2
    End If 'fin de la condition 1
Next I 'prochaine ligne de la boucle
 
If DM = 0 Then
Me.TextBox2.Value = "Pas de data"
Me.TextBox3.Value = "Pas de data"
Else
Me.TextBox2.Value = CDate(DM) 'renvoie la date DM dans la TextBox2
Me.TextBox3.Value = T 'renvoie le taux T dans la TextBox3
End If
 
End Sub

A priori, après plusieurs tests sur plusieurs devises, ce code fonctionne parfaitement. Merci pour l'aide Thautheme !

@Pierre Fauconnier, ton premier lien me ramène à une erreur not found, alors qu'il m’intéresse beaucoup.
Et merci beaucoup pour tous ces codes, je vais les essayer tout de suite !
Je me doute bien que mon classeur est loin d'être optimisé. Le pire, c'est que vous n'aviez ici qu'un exemple. Le vrai fichier sur lequel je dois travailler contient bien plus de colonne, d'informations, et tout est en désordre.
Je ne savais même pas qu'il existait des macros pour faire des tris
Je vais faire au mieux avec ces éléments, merci encore.

Je ne savais pas qu'une variable déclarée comme long était égale à 0 si elle n'était pas définie.
Effectivement, c'était bien ce point qui posait mon incompréhension.

@Docmarti, j'étais fière d'avoir su rectifier ce point seule, mais merci pour ton aide haha!

Merci pour votre aide à tous !

[Pierre Fauconnier]

Pour compléter mon propos...

Voici les codes pour:

• trier la liste des taux sur la devise puis la date;
• recréer la liste des devises triées et sans doublons;
• vérifier qu'une date a bien été saisie (La fonction est minimaliste et tient compte, d'abord, des paramètres régionaux, on peut faire plus compliqué);
• déporter le calcul du taux hors du userform.

On voit que ces codes sont indépendants du userform, car ce n'est pas de la responsabilité de ce dernier de traiter tout cela.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Function GetRate(Cur As String, RateDate As Date) As Variant
  Dim Formula As String
 
  Formula = "=IFERROR(INDEX(t_Taux[Taux],MATCH(1,(t_Taux[Devise]=""{currency}"")*(t_Taux[Date]={date}),1)),""N/A"")"
  Formula = Replace(Replace(Formula, "{currency}", Cur), "{date}", CDate(RateDate) * 1)
  GetRate = Evaluate(Formula)
End Function
 
 
Sub SortRates()
  With Range("t_taux").ListObject.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("t_taux[Devise]"), SortOn:=xlSortValues, Order:=xlAscending
    .SortFields.Add Key:=Range("t_taux[Date]"), SortOn:=xlSortValues, Order:=xlAscending
    .Apply
  End With
End Sub
 
Sub CreateCurrenciesList()
  If Not Range("t_Devises").ListObject.DataBodyRange Is Nothing Then Range("t_Devises").ListObject.DataBodyRange.Delete
  Range("t_Taux[Devise]").Copy Destination:=Range("t_devises[devise]")
  Range("t_devises").RemoveDuplicates 1
  With Range("t_Devises").ListObject.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("t_Devises[devise]"), SortOn:=xlSortValues, Order:=xlAscending
    .Apply
  End With
End Sub
 
Function StringToDate(Value As String) As Date
  If IsDate(Value) Then StringToDate = CDate(Value)
End Function

Dès lors, le lancement du userform est adapté pour préparer les listes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Sub Test()
  SortRates
  CreateCurrenciesList
  With UserForm2
    .cboCurrency.List = Range("t_Devises[devise]").Value
    .Show
  End With
End Sub

Le userform est lui aussi adapté pour utiliser les fonctions du module standard

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Option Explicit
 
Private Sub btnValidate_Click()
  If DatasAreOk Then
    Calculate
  Else
    MsgBox "Le calcul n'a pu être effectué. Veuillez vérifier vos saisies"
  End If
End Sub
 
Sub Calculate()
  tboRate = GetRate(cboCurrency.Value, CDate(tboDate) * 1)
End Sub
 
Function DatasAreOk() As Boolean
  Dim DateValue As Date
 
  DateValue = StringToDate(tboDate.Value)
  DatasAreOk = DateValue <> 0
End Function

[Pierre Fauconnier]

J'ai corrigé le lien dans mon message d'origine. Je te le remets ici: https://fauconnier.developpez.com/tu...ux-structures/