eviter des longues lignes d'instructions IF...ELSEIF....END IF

Question

Bonjour, 

Voici le code que j'aimerais réduire. En fonction du mot contenu dans la cellule ((cellule que j'ai liée à une combobox)(9,16)), le code attribue une couleur à une forme. Mais je me rends compte que ce sera fastidieux décrire toutes ces lignes pour chaque valeur de ma cellule. QUE FAIRE?

Sub listederoulante ()
concession = Cells(6, 19).Value

If (concession = "F1") Then
n = 200
m = 100
i = 1
ElseIf (concession = "L1") Then
n = 0
m = 10
i = 100
ElseIf (concession = "M1") Then
n = 100
m = 0
i = 100
ElseIf (concession = "K1") Then
n = 0
m = 100
i = 150
ElseIf (concession = "T1") Then
n = 30
m = 30
i = 30
ElseIf (concession = "Y1") Then
n = 10
m = 10
i = 10
ElseIf (concession = "E1") Then
n = 0
m = 100
i = 100
ElseIf (concession = "S1") Then
n = 10
m = 10
i = 100
ElseIf (concession = "B1") Then
n = 100
m = 10
i = 100
ElseIf (concession = "E1") Then
n = 10
m = 150
i = 100
ElseIf (concession = "N1") Then
n = 15
m = 10
i = 100
ElseIf (concession = "M1") Then
n = 155
m = 10
i = 100
ElseIf (concession = "E1") Then
n = 10
m = 10
i = 100
End If

Configuration: Windows / Internet Explorer 11.0

michel_m · Answer

Bonjour 
il existe l'instruction "select case". 

voir l'aide en ligne



Select case concessioncase "F1"  n = 200   m = 100    i = 1 case"L1"n=0etcend select


 
 Michel

gbinforme · Answer

Bonjour, 

Tu peux simplifier en rentrant tes paramètres en constantes
Sub listederoulante()
Const pc = "F1,L1,M1,K1,T1,Y1,E1,S1,B1,E1,N1,M1,E1,"
Const pi = "1,100,100,150,30,10,100,100,100,100,100,100,100,"
Const pm = "100,10,0,100,30,10,100,10,10,150,10,10,10,"
Const pn = "200,0,100,0,30,10,0,10,100,10,15,155,10,"
Dim pos As Long, concession As String, i As Long, n As Long, m As Long, tbp
concession = UCase(Cells(6, 19).Value)
pos = InStr(pc, concession & ",")
tbp = Split(Left(pc, pos), ",")
If UBound(tbp) >= 0 Then
    n = Split(pn, ",")(UBound(tbp))
    m = Split(pm, ",")(UBound(tbp))
    i = Split(pi, ",")(UBound(tbp))
Else
    MsgBox "paramétres erronés"
End If
End Sub

Eviter des longues lignes d'instructions IF...ELSEIF....END IF

2 réponses

Discussions similaires

Newsletters