Dites, y aurait-t-il une meilleure fa=E7on d'=E9crire ce qui suit ?
If ComboBox1 =3D [BF13].Value And ComboBox2 =3D [BG13].Value And
ComboBox3 =3D [BH13].Value Then TextBox3.Text =3D [BJ13].Value
If ComboBox1 =3D [BF14].Value And ComboBox2 =3D [BG14].Value And
ComboBox3 =3D [BH14].Value Then TextBox3.Text =3D [BJ14].Value
If ComboBox1 =3D [BF15].Value And ComboBox2 =3D [BG15].Value And
ComboBox3 =3D [BH15].Value Then TextBox3.Text =3D [BJ15].Value
If ComboBox1 =3D [BF16].Value And ComboBox2 =3D [BG16].Value And
ComboBox3 =3D [BH16].Value Then TextBox3.Text =3D [BJ16].Value
If ComboBox1 =3D [BF17].Value And ComboBox2 =3D [BG17].Value And
ComboBox3 =3D [BH17].Value Then TextBox3.Text =3D [BJ17].Value
If ComboBox1 =3D [BF18].Value And ComboBox2 =3D [BG18].Value And
ComboBox3 =3D [BH18].Value Then TextBox3.Text =3D [BJ18].Value
If ComboBox1 =3D [BF19].Value And ComboBox2 =3D [BG19].Value And
ComboBox3 =3D [BH19].Value Then TextBox3.Text =3D [BJ19].Value
If ComboBox1 =3D [BF20].Value And ComboBox2 =3D [BG20].Value And
ComboBox3 =3D [BH20].Value Then TextBox3.Text =3D [BJ20].Value
et =E7a continue jusqu'=E0 la ligne 56....=E0 date...
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
MichD
Bonjour,
Tu pourrais essayer de cette manière :
Affecte le type approprié aux variables X, Y, Z et adapte le nom des objets selon ceux de ton application La procédure suppose qu'il n'y a qu'une combinaison gagnante!
'------------------------------------ Sub test() Dim Rg As Range, Trouve As Range, Sh As Worksheet Dim X As Integer, Y As String, Z As String
Set Sh = ThisWorkbook.Worksheets("Feuil1")
With Sh X = .ComboBox1.value Y = .ComboBox2.value Z = .ComboBox3.value Set Rg = .Range("BF13:BF56")
With Rg Set Trouve = .Find(What:=X, LookIn:=xlValues, lookAt:=xlWhole) If Not Trouve Is Nothing Then adr = Trouve.Address Do If Trouve.Offset(, 1) = Y And Trouve.Offset(, 2) = Z Then Sh.TextBox3.Text = Trouve.Offset(, 4).Value Exit Sub End If Set Trouve = .FindNext(Trouve) Loop Until Trouve.Address = adr End If End With End With End Sub
MichD -------------------------------------------- "Denys" a écrit dans le message de groupe de discussion :
Bonjour à tous,
Dites, y aurait-t-il une meilleure façon d'écrire ce qui suit ?
If ComboBox1 = [BF13].Value And ComboBox2 = [BG13].Value And ComboBox3 = [BH13].Value Then TextBox3.Text = [BJ13].Value If ComboBox1 = [BF14].Value And ComboBox2 = [BG14].Value And ComboBox3 = [BH14].Value Then TextBox3.Text = [BJ14].Value If ComboBox1 = [BF15].Value And ComboBox2 = [BG15].Value And ComboBox3 = [BH15].Value Then TextBox3.Text = [BJ15].Value If ComboBox1 = [BF16].Value And ComboBox2 = [BG16].Value And ComboBox3 = [BH16].Value Then TextBox3.Text = [BJ16].Value If ComboBox1 = [BF17].Value And ComboBox2 = [BG17].Value And ComboBox3 = [BH17].Value Then TextBox3.Text = [BJ17].Value If ComboBox1 = [BF18].Value And ComboBox2 = [BG18].Value And ComboBox3 = [BH18].Value Then TextBox3.Text = [BJ18].Value If ComboBox1 = [BF19].Value And ComboBox2 = [BG19].Value And ComboBox3 = [BH19].Value Then TextBox3.Text = [BJ19].Value If ComboBox1 = [BF20].Value And ComboBox2 = [BG20].Value And ComboBox3 = [BH20].Value Then TextBox3.Text = [BJ20].Value
et ça continue jusqu'à la ligne 56....à date...
Merci pour votre temps
Denys
Bonjour,
Tu pourrais essayer de cette manière :
Affecte le type approprié aux variables X, Y, Z
et adapte le nom des objets selon ceux de ton application
La procédure suppose qu'il n'y a qu'une combinaison gagnante!
'------------------------------------
Sub test()
Dim Rg As Range, Trouve As Range, Sh As Worksheet
Dim X As Integer, Y As String, Z As String
Set Sh = ThisWorkbook.Worksheets("Feuil1")
With Sh
X = .ComboBox1.value
Y = .ComboBox2.value
Z = .ComboBox3.value
Set Rg = .Range("BF13:BF56")
With Rg
Set Trouve = .Find(What:=X, LookIn:=xlValues, lookAt:=xlWhole)
If Not Trouve Is Nothing Then
adr = Trouve.Address
Do
If Trouve.Offset(, 1) = Y And Trouve.Offset(, 2) = Z Then
Sh.TextBox3.Text = Trouve.Offset(, 4).Value
Exit Sub
End If
Set Trouve = .FindNext(Trouve)
Loop Until Trouve.Address = adr
End If
End With
End With
End Sub
MichD
--------------------------------------------
"Denys" a écrit dans le message de groupe de discussion :
1eb3eb8d-74d8-47c8-b9ce-aca23086196b@l28g2000yqc.googlegroups.com...
Bonjour à tous,
Dites, y aurait-t-il une meilleure façon d'écrire ce qui suit ?
If ComboBox1 = [BF13].Value And ComboBox2 = [BG13].Value And
ComboBox3 = [BH13].Value Then TextBox3.Text = [BJ13].Value
If ComboBox1 = [BF14].Value And ComboBox2 = [BG14].Value And
ComboBox3 = [BH14].Value Then TextBox3.Text = [BJ14].Value
If ComboBox1 = [BF15].Value And ComboBox2 = [BG15].Value And
ComboBox3 = [BH15].Value Then TextBox3.Text = [BJ15].Value
If ComboBox1 = [BF16].Value And ComboBox2 = [BG16].Value And
ComboBox3 = [BH16].Value Then TextBox3.Text = [BJ16].Value
If ComboBox1 = [BF17].Value And ComboBox2 = [BG17].Value And
ComboBox3 = [BH17].Value Then TextBox3.Text = [BJ17].Value
If ComboBox1 = [BF18].Value And ComboBox2 = [BG18].Value And
ComboBox3 = [BH18].Value Then TextBox3.Text = [BJ18].Value
If ComboBox1 = [BF19].Value And ComboBox2 = [BG19].Value And
ComboBox3 = [BH19].Value Then TextBox3.Text = [BJ19].Value
If ComboBox1 = [BF20].Value And ComboBox2 = [BG20].Value And
ComboBox3 = [BH20].Value Then TextBox3.Text = [BJ20].Value
Affecte le type approprié aux variables X, Y, Z et adapte le nom des objets selon ceux de ton application La procédure suppose qu'il n'y a qu'une combinaison gagnante!
'------------------------------------ Sub test() Dim Rg As Range, Trouve As Range, Sh As Worksheet Dim X As Integer, Y As String, Z As String
Set Sh = ThisWorkbook.Worksheets("Feuil1")
With Sh X = .ComboBox1.value Y = .ComboBox2.value Z = .ComboBox3.value Set Rg = .Range("BF13:BF56")
With Rg Set Trouve = .Find(What:=X, LookIn:=xlValues, lookAt:=xlWhole) If Not Trouve Is Nothing Then adr = Trouve.Address Do If Trouve.Offset(, 1) = Y And Trouve.Offset(, 2) = Z Then Sh.TextBox3.Text = Trouve.Offset(, 4).Value Exit Sub End If Set Trouve = .FindNext(Trouve) Loop Until Trouve.Address = adr End If End With End With End Sub
MichD -------------------------------------------- "Denys" a écrit dans le message de groupe de discussion :
Bonjour à tous,
Dites, y aurait-t-il une meilleure façon d'écrire ce qui suit ?
If ComboBox1 = [BF13].Value And ComboBox2 = [BG13].Value And ComboBox3 = [BH13].Value Then TextBox3.Text = [BJ13].Value If ComboBox1 = [BF14].Value And ComboBox2 = [BG14].Value And ComboBox3 = [BH14].Value Then TextBox3.Text = [BJ14].Value If ComboBox1 = [BF15].Value And ComboBox2 = [BG15].Value And ComboBox3 = [BH15].Value Then TextBox3.Text = [BJ15].Value If ComboBox1 = [BF16].Value And ComboBox2 = [BG16].Value And ComboBox3 = [BH16].Value Then TextBox3.Text = [BJ16].Value If ComboBox1 = [BF17].Value And ComboBox2 = [BG17].Value And ComboBox3 = [BH17].Value Then TextBox3.Text = [BJ17].Value If ComboBox1 = [BF18].Value And ComboBox2 = [BG18].Value And ComboBox3 = [BH18].Value Then TextBox3.Text = [BJ18].Value If ComboBox1 = [BF19].Value And ComboBox2 = [BG19].Value And ComboBox3 = [BH19].Value Then TextBox3.Text = [BJ19].Value If ComboBox1 = [BF20].Value And ComboBox2 = [BG20].Value And ComboBox3 = [BH20].Value Then TextBox3.Text = [BJ20].Value
et ça continue jusqu'à la ligne 56....à date...
Merci pour votre temps
Denys
Denys
Bonjour Denis,
Merci beaucoup pour ton aide, c'est très apprécié...
Bonne fin de journée
Denys
Bonjour Denis,
Merci beaucoup pour ton aide, c'est très apprécié...
