Bonsoir ,
Pourrait-on m'expliquer où se trouve l'erreur dans le filtre avancé fournis dans ce fichier excel. En effet je tente de trier automatiquement une plage de données en modifiant les critères d'une autre plage or une erreur 1004 intervient. Quelqu'un pour m'aider s'il vous plait?
Le fichier étant trop volumineux voici le code présent :
Private Sub Worksheet_Change(ByVal Target As Range)
Call Advanced_FilteringASJ_DOCUMENTS_MANQUANTS_PEC
Call Advanced_FilteringASJ_Début_régulariser
End Sub
Public Sub Advanced_FilteringASJ_DOCUMENTS_MANQUANTS_PEC()
Dim LastRow As Long
Dim rng As Range
Set rng = Sheets("NEPASTOUCHER").Range("A1:G8")
For i = 3 To Last(1, rng) 'Loops through until the last Row
RowsCount = Application.WorksheetFunction.CountA(Sheets("NEPASTOUCHER").Range("A" & i & ":G" & i))
If RowsCount = 0 Then CriteriaRowsSet = i - 1 Else CriteriaRowsSet = i
Next i
Sheets("TABLEAU DE SUIVI").Range("A1:AG10000").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("NEPASTOUCHER").Range("A2:G" & CriteriaRowsSet), _
CopyToRange:=Sheets("ASJ AVEC DOCUMENTS MANQUANTS").Range("A3:G3")
End Sub
Public Sub Advanced_FilteringASJ_Début_régulariser()
Dim LastRow As Long
Dim rng As Range
Set rng = Sheets("NEPASTOUCHER").Range("A1:G8")
For i = 3 To Last(1, rng) 'Loops through until the last Row
RowsCount = Application.WorksheetFunction.CountA(Sheets("NEPASTOUCHER").Range("J" & i & ":O" & i))
If RowsCount = 0 Then CriteriaRowsSet = i - 1 Else CriteriaRowsSet = i
Next i
Sheets("TABLEAU DE SUIVI").Range("A1:AG10000").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("NEPASTOUCHER").Range("J2:O" & CriteriaRowsSet), _
CopyToRange:=Sheets("ASJ DEBUT A REGULARISER").Range("B2:G2")
End Sub
Function Last(choice As Long, rng As Range)
' 1 = last row
' 2 = last column
' 3 = last cell
Dim lrw As Long
Dim lcol As Long
Select Case choice
Case 1:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
Case 2:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Case 3:
On Error Resume Next
lrw = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
On Error Resume Next
lcol = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
On Error Resume Next
Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
If Err.Number > 0 Then
Last = rng.Cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0
End Select
End Function