if then plusieurs fois à simplifier

[Cougar]

Bonsoir le forum,

Est-il possible de simplifier :

TextBox1 = Sheets("Sheet1").Range("b2")
TextBox2 = Sheets("Sheet1").Range("b3")
TextBox3 = Sheets("Sheet1").Range("b4")
TextBox4 = Sheets("Sheet1").Range("b5")
TextBox5 = Sheets("Sheet1").Range("b6")
TextBox6 = Sheets("Sheet1").Range("b7")
TextBox7 = Sheets("Sheet1").Range("b8")
TextBox8 = Sheets("Sheet1").Range("b9")
TextBox9 = Sheets("Sheet1").Range("b10")
TextBox10 = Sheets("Sheet1").Range("b11")
TextBox11 = Sheets("Sheet1").Range("b12")
TextBox12 = Sheets("Sheet1").Range("b13")
TextBox13 = Sheets("Sheet1").Range("c2")
TextBox14 = Sheets("Sheet1").Range("c3")
TextBox15 = Sheets("Sheet1").Range("c4")
TextBox16 = Sheets("Sheet1").Range("c5")
TextBox17 = Sheets("Sheet1").Range("c6")
TextBox18 = Sheets("Sheet1").Range("c7")
TextBox19 = Sheets("Sheet1").Range("c8")
TextBox20 = Sheets("Sheet1").Range("c9")
TextBox21 = Sheets("Sheet1").Range("c10")
TextBox22 = Sheets("Sheet1").Range("c11")
TextBox23 = Sheets("Sheet1").Range("c12")
TextBox24 = Sheets("Sheet1").Range("c13")


If TextBox1 = "" Then
TextBox13 = ""
End If
If TextBox2 = "" Then
TextBox14 = ""
End If
If TextBox3 = "" Then
TextBox15 = ""
End If
If TextBox4 = "" Then
TextBox16 = ""
End If
If TextBox5 = "" Then
TextBox17 = ""
End If
If TextBox6 = "" Then
TextBox18 = ""
End If
If TextBox7 = "" Then
TextBox19 = ""
End If
If TextBox8 = "" Then
TextBox20 = ""
End If
If TextBox9 = "" Then
TextBox21 = ""
End If
If TextBox10 = "" Then
TextBox22 = ""
End If
If TextBox11 = "" Then
TextBox23 = ""
End If
If TextBox12 = "" Then
TextBox24 = ""
End If

Merci

 

[Papou-net]

Re : if then plusieurs fois à simplifier

Bonsoir le forum,

Est-il possible de simplifier :

TextBox1 = Sheets("Sheet1").Range("b2")
TextBox2 = Sheets("Sheet1").Range("b3")
TextBox3 = Sheets("Sheet1").Range("b4")
TextBox4 = Sheets("Sheet1").Range("b5")
TextBox5 = Sheets("Sheet1").Range("b6")
TextBox6 = Sheets("Sheet1").Range("b7")
TextBox7 = Sheets("Sheet1").Range("b8")
TextBox8 = Sheets("Sheet1").Range("b9")
TextBox9 = Sheets("Sheet1").Range("b10")
TextBox10 = Sheets("Sheet1").Range("b11")
TextBox11 = Sheets("Sheet1").Range("b12")
TextBox12 = Sheets("Sheet1").Range("b13")
TextBox13 = Sheets("Sheet1").Range("c2")
TextBox14 = Sheets("Sheet1").Range("c3")
TextBox15 = Sheets("Sheet1").Range("c4")
TextBox16 = Sheets("Sheet1").Range("c5")
TextBox17 = Sheets("Sheet1").Range("c6")
TextBox18 = Sheets("Sheet1").Range("c7")
TextBox19 = Sheets("Sheet1").Range("c8")
TextBox20 = Sheets("Sheet1").Range("c9")
TextBox21 = Sheets("Sheet1").Range("c10")
TextBox22 = Sheets("Sheet1").Range("c11")
TextBox23 = Sheets("Sheet1").Range("c12")
TextBox24 = Sheets("Sheet1").Range("c13")


If TextBox1 = "" Then
TextBox13 = ""
End If
If TextBox2 = "" Then
TextBox14 = ""
End If
If TextBox3 = "" Then
TextBox15 = ""
End If
If TextBox4 = "" Then
TextBox16 = ""
End If
If TextBox5 = "" Then
TextBox17 = ""
End If
If TextBox6 = "" Then
TextBox18 = ""
End If
If TextBox7 = "" Then
TextBox19 = ""
End If
If TextBox8 = "" Then
TextBox20 = ""
End If
If TextBox9 = "" Then
TextBox21 = ""
End If
If TextBox10 = "" Then
TextBox22 = ""
End If
If TextBox11 = "" Then
TextBox23 = ""
End If
If TextBox12 = "" Then
TextBox24 = ""
End If

Merci

Bonsoir cougar,

Si tes zones de texte sont dans un userform, tu peux créer une boucle :

For n=1 To 12
  If Me.Controls("TextBox" & n).Value="" Then
    Me.Controls("TextBox" & n+12).Value=""
  End If
Next

Espérant t'avoir aidé.

Cordialement.

 

[Cougar]

Re : if then plusieurs fois à simplifier

Bonsoir Papou-net,

J'avais essayé cela sans succès :

For j = 1To 12
If IsNumeric(Controls("TextBox" & j)) Then
Controls("TextBox" & j) = ""
End If
Next

le fait d'ajouter le +12 est super.

Merci

 

[soenda]

Re : if then plusieurs fois à simplifier

Bonsoir le fil, Cougar, Papou-net

Une autre solution:

Pour la première partie, tu peux utiliser la propriété ControlSource des TextBox.
Ex. pour TextBox1 : ControlSource = Sheet1!B2

Pour la 2ème partie (si tu as utilisé la méthode ci-dessus)

    Sheets("Sheet1").[B2:B13].SpecialCells(xlCellTypeBlanks).Select
    Selection.Copy Selection.Offset(, 1)

A plus