Next sans for VBA Excel

Function b2()

Dim repertoire As String
Dim fichier As String
Dim l As Integer
Dim i As Integer
Dim j As Integer


Application.DisplayAlerts = False

repertoire = "C:\Documents and Settings\FX056065\Bureau\Stage\Analyse existant\Contrôles\Fichiers controles + formalismes\TOUS LES FICHIERS CONTROLE"
fichier = ""

For l = 1 To 345
    
    fichier = Sheets("Feuil1").Cells(l, 2).Value
    If fichier <> "" And Cells(l, 3).Value <> "" Then
    
        ChDir repertoire
        
        Workbooks.Open Filename:=repertoire & "\" & fichier 
        Windows(fichier).Activate   'le met à l écran
        
        For i = 15 To 25
            For j = 2 To 3
                If Sheets(1).Cells(2, 1).Value = "Désignation :" Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 1"
                ElseIf Sheets(1).Cells(2, 2).Value = "REFERENCE:  " And Sheets(1).Cells(1, 8).Value = "Désignation" Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 2"
                ElseIf Sheets(1).Cells(2, 3).Value = "Désignation" And Sheets(1).Cells(3, 3).Value = "Référence" Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 3"
                ElseIf Sheets(1).Cells(2, 2).Value = "REFERENCE:  " And Sheets(1).Cells(1, 2).Value = "N° OF:" And Sheets(1).Cells(1, 17).Value <> 1 And Sheets(1).Cells(1, 29).Value = "" Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 4"
                ElseIf Sheets(1).Cells(2, 2).Value = "REFERENCE:  " And Sheets(1).Cells(1, 2).Value = "N° OF:" And Sheets(1).Cells(1, 17).Value = "N° OF:" Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 4bis"
                ElseIf Sheets(1).Cells(2, 3).Value = "REFERENCE:  " And Sheets(1).Cells(1, 3).Value = "N° OF:" Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 4tierce"
                ElseIf Sheets(1).Cells(2, 2).Value = "REFERENCE:  " And Sheets(1).Cells(2, 15).Value = "REFERENCE:  " Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 4-4"
                ElseIf Sheets(1).Cells(2, 2).Value = "REFERENCE:  " And Sheets(1).Cells(2, 17).Value = "REFERENCE:  " Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 4-5"
                ElseIf Sheets(1).Cells(2, j).Value = "REFERENCE:  " And Sheets(1).Cells(2, i).Value = "REFERENCE:  " Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 4-i"
                    Cells(l, 4).Value = j
                    Cells(l, 5).Value = i
                ElseIf Sheets(1).Cells(2, 2).Value = "REFERENCE:  " And Sheets(1).Cells(1, 2).Value = "N° OF:" And Sheets(1).Cells(6, 1).Value = 1 Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 5"
                ElseIf Sheets(1).Cells(2, 2).Value = "REFERENCE:  " And Sheets(1).Cells(1, 2).Value = "LOT " Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 6"
                ElseIf Sheets(1).Cells(2, 2).Value = "REFERENCE:  " And Sheets(1).Cells(1, 2).Value = "N° OF:" And Sheets(1).Cells(1, 29).Value = "N° OF:" Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 8"
                ElseIf Sheets(1).Cells(1, 3).Value = "OF n°" And Sheets(1).Cells(1, 28).Value = "N° piece" Then
                    Workbooks("Type de formalisme.xls").Activate
                    Cells(l, 3).Value = "Type 9"
        
                End If
            Next j
        Next i
        
              

        
        Windows("Type de formalisme.xls").Activate
        Windows(fichier).Activate
        ActiveWindow.Close              
        Windows("Type de formalisme.xls").Activate
    
    End If
    Windows("Type de formalisme.xls").Activate
    
Next l

Application.DisplayAlerts = True
        
End Function

For i = 1 to 18
For j = 3 to 5
If cells(i, 1) = 3 Then
Cells(i, j) = "AB"
Else
Cells(i, j) = "ZE"
End If
nextj
next i

For i = 1 to 18
    For j = 3 to 5
        If cells(i, 1) = 3 Then
            Cells(i, j) = "AB"
        Else
            Cells(i, j) = "ZE"
        End If
    nextj
next i

For l = 1 To 345 

fichier = Sheets("Feuil1").Cells(l, 2).Value 
If fichier <> "" And Cells(l, 3).Value <> "" Then 

'bla bla bla

Next j 
Next i 

Windows("Type de formalisme.xls").Activate 
Windows(fichier).Activate 
ActiveWindow.Close 'ferme le fichier 

Next l