Bonjour,
Le code suivant permet de supprimer les lignes qui ne sont pas comprise entre les dates rentrees par l'utilisateur. Mon probleme se situe au niveau de la ligne 37, Vba me renvoie l'erreur overflow.
J'ai d'abord pense que le probleme se situe au niveau de la variable D1 et j'ai donc passe en Long toutes mes variables. Mais l'erreur a persistee du coup je pense que le probleme est au niveau de la worsheets, j'ai verifie le nom est le bon du coup je comprends pas ou ca bloque.


Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
Private Sub CommandButton1_Click()
Dim D1 As Long
Dim D2 As Long
Dim EndNolig As Long
Dim Nolig As Long
Dim D1Lig As Long
Dim D2Lig As Long
Dim compt As Boolean
Dim j As Long
Dim i As Long
'****************REFRESH****************
Dim lastrow As Long
Worksheets("Do not Alter 501 source").Activate
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 2
Range("A1:D" & lastrow).Copy
Worksheets("Do not Alter 501").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
Application.ScreenUpdating = False
Application.Calculation = False
'*******************Date creation**********
EndNolig = Worksheets("TED Defects 501").Range("A" & Rows.Count).End(xlUp).Row
For Nolig = EndNolig To 2 Step -1
    Worksheets("Do not Alter 501").Cells(Nolig + 2, 4).Value = Worksheets("TED Defects 501").Cells(Nolig, 1).Value
Next
EndNolig = Worksheets("TED Defects 501").Range("A" & Rows.Count).End(xlUp).Row + 2
 
For Nolig = EndNolig To 2 Step -1
    Worksheets("Do not Alter 501").Cells(Nolig, 4).Value = Val(Replace(Cells(Nolig, 4), "/", ""))
Next
'******************** tri****************************
D1 = UserForm1.TextBox1.Value
D2 = UserForm1.TextBox2.Value
Nolig = EndNolig
For Nolig = EndNolig To 3 Step -1
 
    If Worksheets("Do not Alter 501").Cells(Nolig, 4).Value = D1 Then
        D1Lig = Nolig
    End If
    If Worksheets("Do not Alter 501").Cells(Nolig, 4).Value = D2 And compt = 0 Then
        D2Lig = Nolig
        compt = 1
    End If
    Nolig = Nolig - 1
Next
 
For j = EndNolig To D2Lig + 1 Step -1
    Worksheets("Do not Alter 501").Cells(j, 1).EntireRow.Delete Shift:=xlUp
Next
For i = D1Lig - 1 To 4 Step -1
    Worksheets("Do not Alter 501").Cells(i, 1).EntireRow.Delete Shift:=xlUp
Next
UserForm1.Hide
Application.ScreenUpdating = True
Application.Calculation = True
End Sub
De plus sur une ancienne version du code l'erreur n'apparait pas voici le code:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Private Sub CommandButton1_Click()
Dim D1 As Long
Dim D2 As Long
Dim EndNolig As Long
Dim Nolig As Long
Dim D1Lig As Long
Dim D2Lig As Long
Dim compt As Boolean
Dim j As Long
Dim i As Long
'****************REFRESH****************
Dim lastrow As Long
Worksheets("Do not Alter 501 source").Activate
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 2
Range("A1:D" & lastrow).Copy
Worksheets("Do not Alter 501").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
Application.ScreenUpdating = False
Application.Calculation = False
 
EndNolig = Worksheets("TED Defects 501").Range("A" & Rows.Count).End(xlUp).Row + 2
 
D1 = UserForm1.TextBox1.Value
D2 = UserForm1.TextBox2.Value
Nolig = EndNolig
For Nolig = EndNolig To 3 Step -1
 
    If Worksheets("Do not Alter 501").Cells(Nolig, 4).Value = D1 Then
        D1Lig = Nolig
    End If
    If Worksheets("Do not Alter 501").Cells(Nolig, 4).Value = D2 And compt = 0 Then
        D2Lig = Nolig
        compt = 1
    End If
    Nolig = Nolig - 1
Next
 
For j = EndNolig To D2Lig + 1 Step -1
    Worksheets("Do not Alter 501").Cells(j, 1).EntireRow.Delete Shift:=xlUp
Next
For i = D1Lig - 1 To 4 Step -1
    Worksheets("Do not Alter 501").Cells(i, 1).EntireRow.Delete Shift:=xlUp
Next
UserForm1.Hide
Application.ScreenUpdating = True
Application.Calculation = True
End Sub
Pouvez vous m'expliquer pourquoi ca ne marche pas ?

Cordialement
Adam