Procédure trop grande

Le 14/05/2014 à 13:49

Bonjour les experts

Il a-t-il une écriture plus simplifié pour la procédure suivante

Je dois la répéter 10 fois (avec des cellules différentes) et forcement j’ai une erreur de compilation « Procédure trop grande »

Merci pour votre aide

If Sheets("ICT").Range("A1").Value = "" Or Sheets("ICT").Range("A1").Value = 0 Then
TextBox1.Visible = False
Else

If Sheets("ICT").Range("c1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value
ElseIf Sheets("ICT").Range("d1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value
ElseIf Sheets("ICT").Range("e1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value

ElseIf Sheets("ICT").Range("f1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value

ElseIf Sheets("ICT").Range("g1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value

ElseIf Sheets("ICT").Range("h1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value

ElseIf Sheets("ICT").Range("i1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value & " - " & Sheets("ICT").Range("h1").Value

ElseIf Sheets("ICT").Range("j1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value & " - " & Sheets("ICT").Range("h1").Value & _
" - " & Sheets("ICT").Range("i1").Value

ElseIf Sheets("ICT").Range("k1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value & " - " & Sheets("ICT").Range("h1").Value & _
" - " & Sheets("ICT").Range("i1").Value & " - " & Sheets("ICT").Range("j1").Value

ElseIf Sheets("ICT").Range("l1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value & " - " & Sheets("ICT").Range("h1").Value & _
" - " & Sheets("ICT").Range("i1").Value & " - " & Sheets("ICT").Range("j1").Value & " - " & Sheets("ICT").Range("k1").Value

ElseIf Sheets("ICT").Range("m1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value & " - " & Sheets("ICT").Range("h1").Value & _
" - " & Sheets("ICT").Range("i1").Value & " - " & Sheets("ICT").Range("j1").Value & " - " & Sheets("ICT").Range("k1").Value & " - " & Sheets("ICT").Range("l1").Value

ElseIf Sheets("ICT").Range("n1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value & " - " & Sheets("ICT").Range("h1").Value & _
" - " & Sheets("ICT").Range("i1").Value & " - " & Sheets("ICT").Range("j1").Value & " - " & Sheets("ICT").Range("k1").Value & " - " & Sheets("ICT").Range("l1").Value & _
" - " & Sheets("ICT").Range("m1").Value

ElseIf Sheets("ICT").Range("o1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value & " - " & Sheets("ICT").Range("h1").Value & _
" - " & Sheets("ICT").Range("i1").Value & " - " & Sheets("ICT").Range("j1").Value & " - " & Sheets("ICT").Range("k1").Value & " - " & Sheets("ICT").Range("l1").Value & _
" - " & Sheets("ICT").Range("m1").Value & " - " & Sheets("ICT").Range("n1").Value

ElseIf Sheets("ICT").Range("p1").Value = "" Then
TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value & " - " & Sheets("ICT").Range("h1").Value & _
" - " & Sheets("ICT").Range("i1").Value & " - " & Sheets("ICT").Range("j1").Value & " - " & Sheets("ICT").Range("k1").Value & " - " & Sheets("ICT").Range("l1").Value & _
" - " & Sheets("ICT").Range("m1").Value & " - " & Sheets("ICT").Range("n1").Value & " - " & Sheets("ICT").Range("o1").Value
Else

TextBox1.Value = Sheets("ICT").Range("A1").Value & " " & Sheets("ICT").Range("b1").Value & "  " & Sheets("ICT").Range("c1").Value & " - " & Sheets("ICT").Range("d1").Value & _
" - " & Sheets("ICT").Range("e1").Value & " - " & Sheets("ICT").Range("f1").Value & " - " & Sheets("ICT").Range("g1").Value & " - " & Sheets("ICT").Range("h1").Value & _
" - " & Sheets("ICT").Range("i1").Value & " - " & Sheets("ICT").Range("j1").Value & " - " & Sheets("ICT").Range("k1").Value & " - " & Sheets("ICT").Range("l1").Value & _
" - " & Sheets("ICT").Range("m1").Value & " - " & Sheets("ICT").Range("n1").Value & " - " & Sheets("ICT").Range("o1").Value & " - " & Sheets("ICT").Range("p1").Value
End If
End If
Le 14/05/2014 à 21:56

Bonsoir,

D'accord on est des experts... mais on n'a pas de boule de cristal !

Sacrée manie que vous avez de nous refiler une rondelle du saucisson et puis izonka se démerder...

Une procédure commence par Sub ...() et se termine par End Sub

Le moins que vous puissiez faire est de nous refiler toute la procédure... ça nous inspirera peut-être les renseignements que vous ne nous donnez pas...

Et puis si vraiment vous avez envie d'avoir une réponse, vous nous donnez un petit classeur support... ça multipliera vos chances...

En attendant croisez les doigts que Mme Soleil m'aie légué ses pouvoirs...

J'ai supposé que le TextBox était dans un Userform :

Le code KIVABIEN :

Dim Arr(1 To 16)

Private Sub UserForm_Initialize()
Dim k%, x%
For x = 1 To 16
Arr(x) = Sheets("ICT").Cells(1, x).Value
Next
If Arr(1) = 0 Then
TextBox1.Visible = False
Else
For k = 3 To 16
If Arr(k) = "" Then Exit For
Next
TextBox1 = ZK(k)
End If
End Sub

Function ZK(i%)
Select Case i

Case 3: s = Arr(1) & " " & Arr(2)
Case 4: s = ZK(3) & " " & Arr(3)
Case 5: s = ZK(4) & " - " & Arr(4)
Case 6: s = ZK(5) & " - " & Arr(5)
Case 7: s = ZK(6) & " - " & Arr(6)
Case 8: s = ZK(7) & " - " & Arr(7)
Case 9: s = ZK(8) & " - " & Arr(8)
Case 10: s = ZK(9) & " - " & Arr(9)
Case 11: s = ZK(10) & " - " & Arr(10)
Case 12: s = ZK(11) & " - " & Arr(11)
Case 13: s = ZK(12) & " - " & Arr(12)
Case 14: s = ZK(13) & " - " & Arr(13)
Case 15: s = ZK(14) & " - " & Arr(14)
Case 16: s = ZK(15) & " - " & Arr(15)
Case 17: s = ZK(16) & " - " & Arr(16)

End Select
ZK = s
End Function

A+

Rechercher des sujets similaires à "procedure trop grande"