Comment eviter de faire un Select Case avec beaucoup de valeur

**jam92400** · 03/11/2010, 18h25

Bonjour

J'ai une macro qui est un formulaire de saisie de valeur et qui crée un fichier texte avec ces valeurs.

Je dois transformer certaines information de se formulaire dans le fichier texte c'est à dire que si la personne choisit dans la liste "Système de comptabilité" et bien dans le formulaire je vais transformer cette valeur en "SC".

Le probleme c'est que j'ai beaucoup de valeur à "transcoder" et je voudrais eviter de faire des "Select Case" interminable. en revanche j'ai toutes les correspondance sur des feuilles Excel, avez un moyen a me proposer plus optimal ?

Merci

**jfontaine** · 03/11/2010, 18h35

Bonjour,

Tu peux utiliser la fonction recherchev d'excel en VBA en utilisant une variable pour passer la valeur a rechercher

Code :

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

1
2
3
Dim strvaleur As String
 
strvaleur = Application.WorksheetFunction.VLookup("Système de comptabilité", Range("A1:B500"), 2, 0)

**jam92400** · 03/11/2010, 18h57

C'est justement pour ne pas à avoir a écrire toutes les valeurs que je voudrais un truc,je voudrais l'equivalent d'une jointure en sql pour eviter le code ci-dessous ( car là cest juste un echantillon j ai encore une cinquante de valeur)

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
45
46
47
48
49
50
51
52
 
 
 
Select Case CSEmetteur
    Case "ACI = SA de comptabilité Inventaire"
         Systeme_Emetteur = "ACI"
    Case "Administration du personnel"
         Systeme_Emetteur = "ADP"
    Case "SA AXAPAC"
         Systeme_Emetteur = "AXAPAC"
    Case "SA AXIOME V1"
         Systeme_Emetteur = "AXV1"
    Case "SA AXIOME V2"
         Systeme_Emetteur = "AXV2"
    Case "SA AXIOME V4"
         Systeme_Emetteur = "AXV4"
    Case "Comptabilité des Opération d'Assurances"
         Systeme_Emetteur = "COA"
    Case "Comptabilité Inventaire"
         Systeme_Emetteur = "CPTINV"
    Case "SA Datamart UJ"
         Systeme_Emetteur = "DMTUJ"
    Case "Directions techniques"
         Systeme_Emetteur = "DT"
    Case "Equipe rentes individuelles"
         Systeme_Emetteur = "ERI"
    Case "SA Datamart FIDJI"
         Systeme_Emetteur = "FIDJI"
    Case "SA HR ACCESS"
         Systeme_Emetteur = "HR ACCESS"
    Case "SA Interfaceur, Compléteur et Générateur"
         Systeme_Emetteur = "ICG"
    Case "SA Infocentre"
         Systeme_Emetteur = "INFOC"
    Case "SA Datawarehouse et Datmart de l'Inventaire Permanent"
         Systeme_Emetteur = "IP"
    Case "SA MACAO + SARI + JCL DF"
         Systeme_Emetteur = "MACAO"
    Case "SA NOVA"
         Systeme_Emetteur = "NOVA"
    Case "SA RCV + Tronc commun"
         Systeme_Emetteur = "RCV"
    Case "SA RDU"
         Systeme_Emetteur = "RDU"
    Case "SA RPS"
         Systeme_Emetteur = "RPS"
    Case "RXD"
         Systeme_Emetteur = "RXD"
    Case ""
         Systeme_Emetteur = ""
    Case "SA Comptable"
         Systeme_Emetteur = "SCG"

**jfontaine** · 03/11/2010, 19h00

en revanche j'ai toutes les correspondance sur des feuilles Excel, avez un moyen a me proposer plus optimal ?

C'est pour cela que je t'ai proposé Vlookup

tu peux remplacer le texte de recherche par ta variable et la plage par l'endroit ou est stocké ta liste

Code :

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

strvaleur = Application.WorksheetFunction.VLookup(CSEmetteur, Range("A1:B500"), 2, 0)

Cette méthode d'exonere du select case a rallonge

**jam92400** · 03/11/2010, 19h05

ah ok !!

Je viens d'essayer: j'ai mis sur ma sheet1 l'ensemble des codes attendu de A1 à A23 et de B1 a B23 leur libellé correspondant

j'ai mis dans le code:

Code :

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

Strvaleur = Application.WorksheetFunction.VLookup(CSEmetteur, Range("A1:B23"), 2, 0)

Mais il me plante une erreur:

Impossible de lire la propriété VLookup de la classe WorkSheetFunction

en tout cas merci pour ta reponse rapide !

**Godzestla** · 04/11/2010, 11h47

Bonjour,

essaie de montrer plus qu'une ligne de ton code, car si l'erreur vient d'ailleurs, comment veux-tu que quelqu'un puisse le savoir !

**jfontaine** · 04/11/2010, 11h49

quel est le type de ta variable CSEmetteur ?

**jam92400** · 04/11/2010, 12h10

CSEmetteur est une variable sans type qui recupere des valeurs d'une feuille qui sont des textes et les mets dans une combobox

voilà le code global:

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
 
Private Sub Enregistrer()
'TDate.Value = Format(Replace(TDate.Value, "/", ""), jjmmaaaa)
TDate = Format(TDate, "ddmmyyyy")
 
 
'------------------correspondance---------------------------------------
 
'Strvaleur = Application.WorksheetFunction.VLookup(CSEmetteur, Range("A1:B23"), 2, 0)
 
'-----------------------------------------------------------------------
 
 
Set FSys = CreateObject("Scripting.FileSystemObject")
Set MonFic = FSys.CreateTextFile(TPath.Value & "\" & CAnnee.Value & "_" & TDate.Value & "_" & CSEmetteur.Value & "_" & CPerimetre.Value & "_" & CProcessus.Value & "_" & CPoint.Value & "_" & CDon.Value & CNumero.Value & ".ind")
 
'------------------------------------------------------------------------------------------------
With MonFic
.writeLine "Annee_Fiscale=" & CAnnee.Value
.writeLine "Date_Donnees=" & TDate.Value
.writeLine "systeme_Emetteur=" & CSEmetteur.Value
.writeLine "Perimetre_Metier=" & CPerimetre.Value
.writeLine "Processus_Metier=" & CProcessus.Value
.writeLine "Point_Cristallisation=" & CPoint.Value
.writeLine "Code_Fichier=" & CDon.Value & CNumero
End With
MsgBox "Le fichier d'index a été crée dans " & vbCr & TPath.Value, vbOKOnly
 
End Sub

**Godzestla** · 04/11/2010, 12h29

Bonjour,

essaie de déclarer une variable String et de l'utiliser comme argument 1 de Vlookup. cela devrait résoudre ton problème.

**jam92400** · 04/11/2010, 12h55

j'ai essayé mais j'ai toujours le même message d'erreur

Code :

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

1
2
3
4
5
 
 
Dim systemeE As String
systemeE = CSEmetteur.Value
strvaleur = Application.WorksheetFunction.VLookup(systemeE, Range("A1:B23"), 2, 0)

**Godzestla** · 04/11/2010, 13h59

J'ai fait pas mal d'essais.

je crois qu'il y a 2 causes possibles :

- Il ne trouve pas dans la table le critère de recherche.
- Le critère de recherche est d'un type différent de la cellulle scannée

J'ai essayé de gérer le fait que c'est not found, pas moyen.

Voici le code que j'ai testé sur une feuille de mes données

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub test()
 
 
If IsError(WorksheetFunction.VLookup(systemeE, ActiveSheet.Range("G50:H100"), 2, 0)) Then
 Debug.Print "crotte"
 Else
strvaleur = WorksheetFunction.VLookup(systemeE, ActiveSheet.Range("G50:H100"), 2, 0)
Debug.Print strvaleur
End If
 
End Sub

j'ai fait des recherches dans le forum, et je ne trouve pas.

**jam92400** · 04/11/2010, 14h41

je te remercie beaucoup pour tous ces tests

dommage qu'on ne trouve pas le probleme

je sens que vais devoir me taper les 50 valeurs à la main avec des select case

grrrrr....

je vais laisser le topic au cas ou quelqu'un a une solution

**Godzestla** · 04/11/2010, 14h46

une solution possible est de passer par une cellule dans laquelle tu mets ta formule de Vlookup basée sur une autre cellule qui contient l'argument de recherche. je l'ai fait.

j'essaie de retrouver le code qui gère aussi le fait que c'est Not found.

--------Edit----

Voilà l'extrait de code qui fait un Evaluate d'un Vlookup. Si sucessfull, on fait un Sumif.

Ce code marche .

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
                'Arg SUMIF Compte-Mois : ex : 6290920-11
                Qry_arg = Trim(Sheet_id) & "-" & Trim(month_id)
                Resu = Evaluate("VLOOKUP(""" & Qry_arg & """,[FILXXX.xls]Pivot_Month!$H$5:$I$1851,2,FALSE)")
                If IsError(Resu) Then
                    Valeur = 0
                Else
                    Resu = Evaluate("SUMIF('[FILXXX.xls]Pivot_Month'!$H$5:$h$1851,""" & Qry_arg & """,'[FILXXX.xls]Pivot_Month'!$d$5:$d$1851)")
                    If IsError(Resu) Then
                       Valeur = 0
                    Else
                        Valeur = Resu / 1000
                        'Summarize
                        Facts(month_id) = Facts(month_id) + Valeur
                    End If

**jam92400** · 04/11/2010, 16h19

excuse moi mais je ne comprend pas ton code

**Godzestla** · 04/11/2010, 17h07

Bon, en vitesse car je suis sur le feu :

1) On construit la zone à rechercher (le pendant de ta variable SystemeE)

Code :

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

Qry_arg = Trim(Sheet_id) & "-" & Trim(month_id)

2) Dans une variable de type variant, on récupère le résultat de l'évaluation' du Vlookup qui recherche Qry_arg dans le fichier FILXXX.XLS, dans la feuille PIVOT_MONTH, dans la plage H5:I1851, renvoie la valeur de la 2° colonne si trouvé, et si pas de matching (FALSE), tombe en erreur.

Code :

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

                Resu = Evaluate("VLOOKUP(""" & Qry_arg & """,[FILXXX.xls]Pivot_Month!$H$5:$I$1851,2,FALSE)")

3) Vu que Resu est variant, elle peut accueillir soit le résultat, soit un status d'erreur. Si erreur, on le sait via la ligne suivante, erreur voulant dire = pas trouvé

Code :

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

If IsError(Resu) Then

4) Sinon Resu contient le résultat recherché, et dans mon cas c'est un montant en 1000EUR et donc je le divise par 1000.

Tu comprends mieux ?

**jam92400** · 05/11/2010, 11h55

euh....

Je vais t'envoyer ma feuille Excel avec la macro c'est plus simple

le mot de passe pour acceder à la macro c'est "admin"

tu trouveras sur la sheet1 dans la colonne A1 à A23 la liste des valeurs que je veux récupérer et en face de B1 à B23 leur libellé.

tu verras dans le code pour le moment j'ai tout fait avec des Select Case à rallonge

**Godzestla** · 05/11/2010, 12h13

Désolé Jamel, je ne le ferai pas à ta place.
Tu as tous les élements pour répondre à ta demande.
A toi de plancher dûr sur le sujet en y passant le temps nécessaire pour comprendre, étape obligatoire pour maitriser le sujet.

Bonne continuation.

**jam92400** · 05/11/2010, 12h18

non non c'est du tout ce que je te demandais c'est juste que tu comprennes mieux ma problématique car dans ta réponse je comprend pas vraiment le lien

donc je voulais te montrer "en vrai" si je peux dire

je t'ai pas demander de faire quoi que ce soit à ma place c'est uniquement pour illustrer de facon de plus clair ma problématique