Erreur 9 VBA
a
Bonjour
voila mon ocde marcher tres bien hier mais je sais pas pourquoi il ne veut plus marcher
il me dis que jai un pb au niveau de :
dernligne = ThisWorkbook.Sheets("Feuil1").Range("A" & .Rows.Count).End(xlUp).Rowsi vous pouviez m'aider merci
ci joint le code entier
Sub MONTANT_INDEMNITE_PRINCIPALE()
Dim Date_Souscription_Adhésion As Range, Statut_Technique_Sinistre As Range
Dim Montant_Ind_Principale As Range
Dim dernligne As Long
Dim i As Integer
Dim sa As String
Dim montant As String
Dim tt1, tt2, tt3, tt4, tt5, tt6, tt7, tt8, tt9, tt10, tt11, tt12 As Double
Dim tt13, tt14, tt15, tt16, tt17, tt18, tt19, tt20, tt21, tt22, tt23, tt24 As Double
Dim tt25, tt26, tt27, tt28, tt29, tt30, tt31, tt32, tt33, tt34, tt35, tt36 As Double
Dim s As Double
Dim Mois As Integer
With Worksheets("Feuil1")
dernligne = ThisWorkbook.Sheets("Feuil1").Range("A" & .Rows.Count).End(xlUp).Row
Set Date_Souscription_Adhésion = ThisWorkbook.Sheets("Feuil1").Range("B2:B" & dernligne)
Set Montant_Ind_Principale = ThisWorkbook.Sheets("Feuil1").Range("E2:E" & dernligne)
Set Statut_Technique_Sinistre = ThisWorkbook.Sheets("Feuil1").Range("D2:D" & dernligne)
End With
i = 2
For i = 2 To dernligne
sa = Cells(i, 4)
montant = Cells(i, 5)
Mois = Month(Cells(i, 2))
If sa <> "Terminé - Refusé après instruction" Then
If Year(Cells(i, 2).Value = 2015) Then
Select Case Mois
Case Is = 1
tt1 = tt1 + CInt(montant)
Case Is = 2
tt2 = tt2 + CInt(montant)
Case Is = 3
tt3 = tt3 + CInt(montant)
Case Is = 4
tt4 = tt4 + CInt(montant)
Case Is = 5
tt5 = tt5 + CInt(montant)
Case Is = 6
tt6 = tt6 + CInt(montant)
Case Is = 7
tt7 = tt7 + CInt(montant)
Case Is = 8
tt8 = tt8 + CInt(montant)
Case Is = 9
tt9 = tt9 + CInt(montant)
Case Is = 10
tt10 = tt10 + CInt(montant)
Case Is = 11
tt11 = tt11 + CInt(montant)
Case Is = 12
tt12 = tt12 + CInt(montant)
End Select
End If
If Year(Cells(i, 2).Value) = 2016 Then
Select Case Mois
Case Is = 13
tt13 = tt13 + CInt(montant)
Case Is = 14
tt14 = tt14 + CInt(montant)
Case Is = 15
tt15 = tt15 + CInt(montant)
Case Is = 16
tt16 = tt16 + CInt(montant)
Case Is = 17
tt17 = tt17 + CInt(montant)
Case Is = 18
tt18 = tt18 + CInt(montant)
Case Is = 19
tt19 = tt19 + CInt(montant)
Case Is = 20
tt20 = tt20 + CInt(montant)
Case Is = 21
tt21 = tt21 + CInt(montant)
Case Is = 22
tt22 = tt22 + CInt(montant)
Case Is = 23
tt23 = tt23 + CInt(montant)
Case Is = 24
tt24 = tt24 + CInt(montant)
End Select
End If
If Year(Cells(i, 2).Value) = 2017 Then
Select Case Mois
Case Is = 25
tt25 = tt25 + CInt(montant)
Case Is = 26
tt26 = tt26 + CInt(montant)
Case Is = 27
tt27 = tt27 + CInt(montant)
Case Is = 28
tt28 = tt28 + CInt(montant)
Case Is = 29
tt29 = tt29 + CInt(montant)
Case Is = 30
tt30 = tt30 + CInt(montant)
Case Is = 31
tt31 = tt31 + CInt(montant)
Case Is = 32
tt32 = tt32 + CInt(montant)
Case Is = 33
tt33 = tt33 + CInt(montant)
Case Is = 34
tt34 = tt34 + CInt(montant)
Case Is = 35
tt35 = tt35 + CInt(montant)
Case Is = 36
tt36 = tt36 + CInt(montant)
End Select
End If
End If
Sheets("Feuil2").Cells(1, 2).Value = "janvier 2015"
Sheets("Feuil2").Cells(1, 3).Value = tt1
Sheets("Feuil2").Cells(2, 2).Value = "fevrier 2015"
Sheets("Feuil2").Cells(2, 3).Value = tt2
Sheets("Feuil2").Cells(3, 2).Value = "mars 2015"
Sheets("Feuil2").Cells(3, 3).Value = tt3
Sheets("Feuil2").Cells(4, 2).Value = "avril 2015"
Sheets("Feuil2").Cells(4, 3).Value = tt4
Sheets("Feuil2").Cells(5, 2).Value = "mai 2015"
Sheets("Feuil2").Cells(5, 3).Value = tt5
Sheets("Feuil2").Cells(6, 2).Value = "juin 2015"
Sheets("Feuil2").Cells(6, 3).Value = tt6
Sheets("Feuil2").Cells(7, 2).Value = "juil 2015"
Sheets("Feuil2").Cells(7, 3).Value = tt7
Sheets("Feuil2").Cells(8, 2).Value = "aout 2015"
Sheets("Feuil2").Cells(8, 3).Value = tt8
Sheets("Feuil2").Cells(9, 2).Value = "sept 2015"
Sheets("Feuil2").Cells(9, 3).Value = tt9
Sheets("Feuil2").Cells(10, 2).Value = "oct 2015"
Sheets("Feuil2").Cells(10, 3).Value = tt10
Sheets("Feuil2").Cells(11, 2).Value = "nov 2015"
Sheets("Feuil2").Cells(11, 3).Value = tt11
Sheets("Feuil2").Cells(12, 2).Value = "dec 2015"
Sheets("Feuil2").Cells(12, 3).Value = tt12
Sheets("Feuil2").Cells(13, 2).Value = "janvier 2016"
Sheets("Feuil2").Cells(13, 3).Value = tt13
Sheets("Feuil2").Cells(14, 2).Value = "fevrier 2016"
Sheets("Feuil2").Cells(14, 3).Value = tt14
Sheets("Feuil2").Cells(15, 2).Value = "mars 2016"
Sheets("Feuil2").Cells(15, 3).Value = tt15
Sheets("Feuil2").Cells(16, 2).Value = "avril 2016"
Sheets("Feuil2").Cells(16, 3).Value = tt16
Sheets("Feuil2").Cells(17, 2).Value = "mai 2016"
Sheets("Feuil2").Cells(17, 3).Value = tt17
Sheets("Feuil2").Cells(18, 2).Value = "juin 2016"
Sheets("Feuil2").Cells(18, 3).Value = tt18
Sheets("Feuil2").Cells(19, 2).Value = "juil 2016"
Sheets("Feuil2").Cells(19, 3).Value = tt19
Sheets("Feuil2").Cells(20, 2).Value = "aout 2016"
Sheets("Feuil2").Cells(20, 3).Value = tt20
Sheets("Feuil2").Cells(21, 2).Value = "sept 2016"
Sheets("Feuil2").Cells(21, 3).Value = tt21
Sheets("Feuil2").Cells(22, 2).Value = "oct 2016"
Sheets("Feuil2").Cells(22, 3).Value = tt22
Sheets("Feuil2").Cells(23, 2).Value = "nov 2016"
Sheets("Feuil2").Cells(23, 3).Value = tt23
Sheets("Feuil2").Cells(24, 2).Value = "dec 2016"
Sheets("Feuil2").Cells(24, 3).Value = tt24
'refaire la mm chose pour 2017
Next i
End Subbonjour,
je n'ai pas de problème avec le fichier que tu as fourni. Tu as probablement modifié le nom de tes onglets dans le fichier qui pose problème.
a
non je fais exactement sur le mm fichier
M
Bonjour,
Ici :
dernligne = ThisWorkbook.Sheets("Feuil1").Range("A" & .Rows.Count).End(xlUp).Rowle point surligné est de trop.
Mais là :
With Worksheets("Feuil1")
dernligne = ThisWorkbook.Sheets("Feuil1").Range("A" & .Rows.Count).End(xlUp).Row
Set Date_Souscription_Adhésion = ThisWorkbook.Sheets("Feuil1").Range("B2:B" & dernligne)
Set Montant_Ind_Principale = ThisWorkbook.Sheets("Feuil1").Range("E2:E" & dernligne)
Set Statut_Technique_Sinistre = ThisWorkbook.Sheets("Feuil1").Range("D2:D" & dernligne)
End Withc'est tout ce qui est surligné qui est de trop.
Cordialement.