Erreur d'éxecution 9

F

fonso

XLDnaute Nouveau
Bonjour à tous,

J'ai un soucis avec excel. Depuis peu, il m'indique le message suivant :

"Erreur d'exécution 9. L'indice n'appartient pas à la sélection."

Après plusieurs heures passées devant mon PC depuis hier après-midi, je me rends compte que mon intelligence me fait défaut ... snif ...

Voici mes lignes de codes :

Aidez moi, je n'en peux plus, je vais exploser !!! :(

--------------------------------------------------------------------------

Private Sub CommandButton27_Click()

If TextBox2 > "" Then

UserForm23.ComboBox1.Value = UserForm31.TextBox267.Value
Sheets("Dossiers").Select
Selection.AutoFilter field:=2, Criteria1:=UserForm23.ComboBox1.Value
Selection.AutoFilter field:=24, Criteria1:=UserForm31.ComboBox2.Value

Sheets("Dossiers").Select
Range("P4").Select
Range("P4:p65536").Select
Selection.Copy
Sheets("Transition5").Select
Range("A1").Select
ActiveSheet.Paste

Sheets("Dossiers").Select
Range("B4:B65536").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transition5").Select
Range("B1").Select
ActiveSheet.Paste

Sheets("Dossiers").Select
Range("AT4:AT65536").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transition5").Select
Range("C1").Select
ActiveSheet.Paste

Sheets("Dossiers").Select
Range("Z4:Z65536").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transition5").Select
Range("D1").Select
ActiveSheet.Paste

Sheets("Dossiers").Select
Range("AC4:AC65536").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transition5").Select
Range("E1").Select
ActiveSheet.Paste

Sheets("Dossiers").Select
Range("AD4:AD65536").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transition5").Select
Range("F1").Select
ActiveSheet.Paste

Sheets("Dossiers").Select
Range("AS4:AS65536").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transition5").Select
Range("H1").Select
ActiveSheet.Paste

Sheets("Dossiers").Select
Range("A4:A65536").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transition5").Select
Range("I1").Select
ActiveSheet.Paste


Sheets("Dossiers").Select
Range("A3").Select
Selection.AutoFilter
Selection.AutoFilter

With Worksheets("Transition5")
UserForm23.TB1.Value = "Stage de sensibilisation à la sécurité routière"
UserForm23.TB2.Value = .Cells(1, 1)
UserForm23.TB4.Value = "Dossier n°" & " " & .Cells(1, 9)
UserForm23.TB6.Value = "Lieu de stage : " & .Cells(1, 4) & " " & "-" & " " & .Cells(1, 6) & " " & "(" & .Cells(1, 5) & ")"
UserForm23.TB7.Value = UserForm31.ComboBox2.Value & " " & "et" & " " & UserForm31.Date2.Value
UserForm23.TB2B.Value = .Cells(1, 3)
UserForm23.TextBox2.Value = 1
UserForm23.Txtdate1.Value = .Cells(1, 7)
UserForm23.ComboBox2.Value = "CHEQUE"
UserForm23.TB61.Value = .Cells(1, 8)

End With

If UserForm23.TextBox1.Value = 1 Then
UserForm23.TextBox78.Value = Val(UserForm23.TB1B)
End If
If UserForm23.TextBox1.Value = 0 Then
UserForm23.TextBox78.Value = ""
End If

If UserForm23.TextBox2.Value = 1 Then
UserForm23.TextBox77.Value = CDbl(UserForm23.TB2B)
End If
If UserForm23.TextBox2.Value = 0 Then
UserForm23.TextBox77.Value = ""
End If

If UserForm23.TextBox3.Value = 1 Then
UserForm23.TextBox76.Value = Val(UserForm23.TB3B)
End If
If UserForm23.TextBox3.Value = 0 Then
UserForm23.TextBox76.Value = ""
End If

If UserForm23.TextBox4.Value = 1 Then
UserForm23.TextBox75.Value = Val(UserForm23.TB4B)
End If
If UserForm23.TextBox4.Value = 0 Then
UserForm23.TextBox75.Value = ""
End If

If UserForm23.TextBox5.Value = 1 Then
UserForm23.TextBox74.Value = Val(UserForm23.TB5B)
End If
If UserForm23.TextBox5.Value = 0 Then
UserForm23.TextBox74.Value = ""
End If

If UserForm23.TextBox6.Value = 1 Then
UserForm23.TextBox73.Value = Val(UserForm23.TB6B)
End If
If UserForm23.TextBox6.Value = 0 Then
UserForm23.TextBox73.Value = ""
End If

If UserForm23.TextBox7.Value = 1 Then
UserForm23.TextBox72.Value = Val(UserForm23.TB7B)
End If
If UserForm23.TextBox7.Value = 0 Then
UserForm23.TextBox72.Value = ""
End If

If UserForm23.TextBox8.Value = 1 Then
UserForm23.TextBox71.Value = Val(UserForm23.TB8B)
End If
If UserForm23.TextBox8.Value = 0 Then
UserForm23.TextBox71.Value = ""
End If

If UserForm23.TextBox9.Value = 1 Then
UserForm23.TextBox70.Value = Val(UserForm23.TB9B)
End If
If UserForm23.TextBox9.Value = 0 Then
UserForm23.TextBox70.Value = ""
End If

If UserForm23.TextBox10.Value = 1 Then
UserForm23.TextBox69.Value = Val(UserForm23.TB10B)
End If
If UserForm23.TextBox10.Value = 0 Then
UserForm23.TextBox69.Value = ""
End If

If UserForm23.TextBox11.Value = 1 Then
UserForm23.TextBox68.Value = Val(UserForm23.TB11B)
End If
If UserForm23.TextBox11.Value = 0 Then
UserForm23.TextBox68.Value = ""
End If

If UserForm23.TextBox12.Value = 1 Then
UserForm23.TextBox67.Value = Val(UserForm23.TB12B)
End If
If UserForm23.TextBox12.Value = 0 Then
UserForm23.TextBox67.Value = ""
End If

If UserForm23.TextBox13.Value = 1 Then
UserForm23.TextBox66.Value = Val(UserForm23.TB13B)
End If
If UserForm23.TextBox13.Value = 0 Then
UserForm23.TextBox66.Value = ""
End If

If UserForm23.TextBox14.Value = 1 Then
UserForm23.TextBox65.Value = Val(UserForm23.TB14B)
End If
If UserForm23.TextBox14.Value = 0 Then
UserForm23.TextBox65.Value = ""
End If

If UserForm23.TextBox15.Value = 1 Then
UserForm23.TextBox64.Value = Val(UserForm23.TB15B)
End If
If UserForm23.TextBox15.Value = 0 Then
UserForm23.TextBox64.Value = ""
End If

UserForm23.TextBox59.Value = Val(UserForm23.TextBox78) + Val(UserForm23.TextBox77) + Val(UserForm23.TextBox76) + Val(UserForm23.TextBox75) + Val(UserForm23.TextBox74) + Val(UserForm23.TextBox73) + Val(UserForm23.TextBox72) + Val(UserForm23.TextBox71) + Val(UserForm23.TextBox70) + Val(UserForm23.TextBox69) + Val(UserForm23.TextBox68) + Val(UserForm23.TextBox67) + Val(UserForm23.TextBox66) + Val(UserForm23.TextBox65) + Val(UserForm23.TextBox64)
UserForm23.TextBox59.Value = Format(UserForm23.TextBox59.Value, "# ##0.00")
UserForm23.TextBox61.Value = Val(UserForm23.TextBox59) * 0.196
UserForm23.TextBox61.Value = Format(UserForm23.TextBox61.Value, "# ##0.00")
UserForm23.TextBox60.Value = Val(UserForm23.TB1B) + Val(UserForm23.TB2B) + Val(UserForm23.TB3B) + Val(UserForm23.TB4B) + Val(UserForm23.TB5B) + Val(UserForm23.TB6B) + Val(UserForm23.TB7B) + Val(UserForm23.TB8B) + Val(UserForm23.TB9B) + Val(UserForm23.TB10B) + Val(UserForm23.TB11B) + Val(UserForm23.TB12B) + Val(UserForm23.TB13B) + Val(UserForm23.TB14B) + Val(UserForm23.TB15B) - Val(UserForm23.TextBox78) - Val(UserForm23.TextBox77) - Val(UserForm23.TextBox76) - Val(UserForm23.TextBox75) - Val(UserForm23.TextBox74) - Val(UserForm23.TextBox73) - Val(UserForm23.TextBox72) - Val(UserForm23.TextBox71) - Val(UserForm23.TextBox70) - Val(UserForm23.TextBox69) - Val(UserForm23.TextBox68) - Val(UserForm23.TextBox67) - Val(UserForm23.TextBox66) - Val(UserForm23.TextBox65) - Val(UserForm23.TextBox64)
UserForm23.TextBox60.Value = Format(UserForm23.TextBox60.Value, "# ##0.00")
UserForm23.TextBox62.Value = Val(UserForm23.TextBox59) + Val(UserForm23.TextBox60) + Val(UserForm23.TextBox61)
UserForm23.TextBox62.Value = Format(UserForm23.TextBox62.Value, "# ##0.00")
UserForm23.TextBox63.Value = Val(UserForm23.TextBox62) - Val(UserForm23.TB61)
UserForm23.TextBox63.Value = Format(UserForm23.TextBox63.Value, "# ##0.00")
UserForm23.TB61.Value = Format(UserForm23.TB61.Value, "# ##0.00")


Dim last
Sheets("Facture").Select
Range("C20").Value = UserForm23.TB1.Value
Range("D21").Value = UserForm23.TB2.Value
Range("D22").Value = UserForm23.TB3.Value
Range("D23").Value = UserForm23.TB4.Value
Range("D24").Value = UserForm23.TB5.Value
Range("D25").Value = UserForm23.TB6.Value
Range("D26").Value = UserForm23.TB7.Value
Range("D27").Value = UserForm23.TB8.Value
Range("D28").Value = UserForm23.TB9.Value
Range("D29").Value = UserForm23.TB10.Value
Range("D30").Value = UserForm23.TB11.Value
Range("D31").Value = UserForm23.TB12.Value
Range("D32").Value = UserForm23.TB13.Value
Range("D33").Value = UserForm23.TB14.Value
Range("D34").Value = UserForm23.TB15.Value

Range("AA20").Value = UserForm23.TB1B.Value
Range("AA21").Value = UserForm23.TB2B.Value
Range("AA22").Value = UserForm23.TB3B.Value
Range("AA23").Value = UserForm23.TB4B.Value
Range("AA24").Value = UserForm23.TB5B.Value
Range("AA25").Value = UserForm23.TB6B.Value
Range("AA26").Value = UserForm23.TB7B.Value
Range("AA27").Value = UserForm23.TB8B.Value
Range("AA28").Value = UserForm23.TB9B.Value
Range("AA29").Value = UserForm23.TB10B.Value
Range("AA30").Value = UserForm23.TB11B.Value
Range("AA31").Value = UserForm23.TB12B.Value
Range("AA32").Value = UserForm23.TB13B.Value
Range("AA33").Value = UserForm23.TB14B.Value
Range("AA34").Value = UserForm23.TB15B.Value

Range("AI20").Value = UserForm23.TextBox1.Value
Range("AI21").Value = UserForm23.TextBox2.Value
Range("AI22").Value = UserForm23.TextBox3.Value
Range("AI23").Value = UserForm23.TextBox4.Value
Range("AI24").Value = UserForm23.TextBox5.Value
Range("AI25").Value = UserForm23.TextBox6.Value
Range("AI26").Value = UserForm23.TextBox7.Value
Range("AI27").Value = UserForm23.TextBox8.Value
Range("AI28").Value = UserForm23.TextBox9.Value
Range("AI29").Value = UserForm23.TextBox10.Value
Range("AI30").Value = UserForm23.TextBox11.Value
Range("AI31").Value = UserForm23.TextBox12.Value
Range("AI32").Value = UserForm23.TextBox13.Value
Range("AI33").Value = UserForm23.TextBox14.Value
Range("AI34").Value = UserForm23.TextBox15.Value

Range("AJ20").Value = UserForm23.TextBox78.Value
Range("AJ21").Value = UserForm23.TextBox77.Value
Range("AJ22").Value = UserForm23.TextBox76.Value
Range("AJ23").Value = UserForm23.TextBox75.Value
Range("AJ24").Value = UserForm23.TextBox74.Value
Range("AJ25").Value = UserForm23.TextBox73.Value
Range("AJ26").Value = UserForm23.TextBox72.Value
Range("AJ27").Value = UserForm23.TextBox71.Value
Range("AJ28").Value = UserForm23.TextBox70.Value
Range("AJ29").Value = UserForm23.TextBox69.Value
Range("AJ30").Value = UserForm23.TextBox68.Value
Range("AJ31").Value = UserForm23.TextBox67.Value
Range("AJ32").Value = UserForm23.TextBox66.Value
Range("AJ33").Value = UserForm23.TextBox65.Value
Range("AJ34").Value = UserForm23.TextBox64.Value

Dim Ladate As Date

Range("B16").Value = "'" & UserForm23.Txtdate1
'"DD/MM/YYYY"

Range("T16").Value = "'" & UserForm23.Txtdate1
'"DD/MM/YYYY"

Range("G16").Value = "'" & UserForm23.TextBox79.Value

Range("B46").Value = UserForm23.TextBox59.Value
Range("G46").Value = UserForm23.TextBox60.Value
Range("L46").Value = UserForm23.TextBox61.Value
Range("Q46").Value = UserForm23.TextBox62.Value
Range("W46").Value = UserForm23.TB61.Value
Range("AC46").Value = UserForm23.TextBox63.Value

Range("Y16").Value = UserForm23.ComboBox2.Value
Range("O16").Value = UserForm23.Nom.Value
Range("R7").Value = UserForm23.Civilité & " " & UserForm23.Nom.Value & " " & UserForm23.Prénom.Value
Range("R8").Value = UserForm23.Adresse.Value
Range("R9").Value = UserForm23.Complt.Value
Range("R10").Value = UserForm23.CP.Value
Range("T10").Value = UserForm23.Ville.Value

Range("R7:AE10").Select
Selection.ClearContents
Range("B10").Select
Selection.ClearContents
Range("B16:F16").Select
Selection.ClearContents
Range("L16:N16").Select
Selection.ClearContents
Range("O16:S16").Select
Selection.ClearContents
Range("T16:X16").Select
Selection.ClearContents
Range("Y16:AG16").Select
Selection.ClearContents
Range("B19:AJ43").Select
Selection.ClearContents
ActiveWindow.LargeScroll Down:=1
Range("B46:AG46").Select
Selection.ClearContents
Range("A46").Select
ActiveWindow.LargeScroll Down:=-1
Range("A20").Select
ActiveWindow.LargeScroll Down:=-1
Range("T23").Select

UserForm23.Show


End If

Sheets("Commandes").Select

End Sub

-------------------------------------------------------------------------

Merci par avance !!!
 
Trier par date Trier par votes
F

fonso

XLDnaute Nouveau
Re : Erreur d'éxecution 9

merci beaucoup Gilbert pour la rapidité de la réponse. Grâce à vous j'ai pu m'en sortir. Si j'avais pas posté le message, j'y aurais passé encore des heures sans même trouver l'erreur.

Encore merci
 
Vote positif 0 Vote négatif

Discussions similaires

G
  • Question
Microsoft 365 Export données
Réponses
4
Affichages
413
GClaire
G
M
  • Question
Microsoft 365 Erreur sur mon Code VBA
Réponses
2
Affichages
297
Gégé-45550
Gégé-45550
L
  • Question
Microsoft 365 Faire agir une macro en fonction de l'emplacement du bouton
Réponses
2
Affichages
291
AtTheOne
AtTheOne
Cédric06400
  • Résolu(e)
XL 2021 Revenir sur Excel
Réponses
6
Affichages
356
Franc58
F
D
  • Question
XL 2021 Filtre Multi Critères Listview VBA
2
Réponses
21
Affichages
1 K
Dranreb
Dranreb
B
  • Résolu(e)
Microsoft 365 creation d'liste à imprimer
2 3 4
Réponses
51
Affichages
979
Banbara
B
Nadou69
  • Résolu(e)
XL 2019 Erreur dans ma macro
Réponses
9
Affichages
293
Nadou69
Nadou69
C
  • Question
XL 2021 Problème de remplissage d'une colonne conditionné à d'autres valeurs
Réponses
2
Affichages
301
CathyLD
C
Regueiro
  • Question
Microsoft 365 VBA Transpose
Réponses
11
Affichages
618
sylvanu
sylvanu
N
  • Résolu(e)
Microsoft 365 Aide pour terminer un code Vba Excel
2
Réponses
23
Affichages
893
job75
job75

Membres actuellement en ligne

5 membres et 74 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
314 499
Messages
2 110 247
Membres
110 711
dernier inscrit
chmessi