Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

Compile Error: Next Without For

2 réponses
Avatar
YeTi
Bonjour à tous,

Je cherche à mettre à jour automatiquement un fichier Excel avec un rapport
Business Objects exporté sous Excel. Le problème c'est que lorsque je lance
ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai bien
un "For" déclaré avant le Next...

Quelqu'un a-t-il une solution?

Le code se trouve ci-dessous

Merci d'avance



Private Sub Workbook_Open()

Application.ScreenUpdating = False

'Checks if the file is on Read Only mode. If it is, the user receives a
notification and the macro stops
If ThisWorkbook.ReadOnly = True Then
MsgBox ("The file is currently in Read Only mode. The macro will not
be executed until you have full access to the file.")
Exit Sub
Else

'If it is not, it checks if the source file exists. If not, the user
receives a notification and the macro stops
If CreateObject("Scripting.FileSystemObject").FileExists _
(Environ("HOMEDRIVE") & Environ("HOMEPATH") &
"\Desktop\BO-OL.XLS") = False Then
MsgBox ("The source file wasn't found. The macro will not be
executed")
Exit Sub
Else

'If both requirements are satisfied, then the macro begins
'We begin by opening the source file
Workbooks.Open Filename:= _
Environ("HOMEDRIVE") & Environ("HOMEPATH") &
"\Desktop\BO-OL.XLS"

'The variable for the loop depends on the number of line there is in the
source file
For i = 3 To Workbooks("BO-OL.XLS").Sheets("Report
1").Range("A1").Value + 2
'If the line of our Order List correspond to the one in the source file, it
goes to the next line
If Workbooks("BO-OL.XLS").Sheets("Report 1").Cells(i, 1) =
ThisWorkbook.Sheets("2009").Cells(i, 1) Then
Next i
Else
'Otherwise we copy the entire line of the source file and paste it at the
same place in our Order List
Workbooks("BO-OL.XLS").Sheets("Report 1").Rows(i).Copy
ThisWorkbook.Sheets("2009").Rows(i).Paste
Next i
Workbooks("BO-OL.XLS").Sheets("Report 1").Columns(2).Copy
ThisWorkbook.Sheets("2009").Columns(2).Paste
Workbooks("BO-OL.XLS").Close
End If
End If
End If

Application.ScreenUpdating = True

End Sub

2 réponses

Avatar
ElXav
Bonjour,

Sortir le "Next" de la structure If.
Il ne doit y avoir qu'un seul Next par For:

Soit

For...

If ... Then
Else
Endif

Next

et donc:

For i = 3 To Workbooks("BO-OL.XLS").Sheets("Report
1").Range("A1").Value + 2
'If the line of our Order List correspond to the one in the source
file, it
goes to the next line
If Workbooks("BO-OL.XLS").Sheets("Report 1").Cells(i, 1)
= ThisWorkbook.Sheets("2009").Cells(i, 1) Then
Else
'Otherwise we copy the entire line of the source file and paste it at
the
same place in our Order List
Workbooks("BO-OL.XLS").Sheets("Report 1").Rows
(i).Copy
ThisWorkbook.Sheets("2009").Rows(i).Paste
ENDIF
Next i
Workbooks("BO-OL.XLS").Sheets("Report 1").Columns(2).Copy
ThisWorkbook.Sheets("2009").Columns(2).Paste
Workbooks("BO-OL.XLS").Close
End If
End If

Cordialement.




On 20 juil, 14:09, YeTi wrote:
Bonjour à tous,

Je cherche à mettre à jour automatiquement un fichier Excel avec un r apport
Business Objects exporté sous Excel. Le problème c'est que lorsque je lance
ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai b ien
un "For" déclaré avant le Next...

Quelqu'un a-t-il une solution?

Le code se trouve ci-dessous

Merci d'avance

Private Sub Workbook_Open()

Application.ScreenUpdating = False

'Checks if the file is on Read Only mode. If it is, the user receives a
notification and the macro stops
    If ThisWorkbook.ReadOnly = True Then
        MsgBox ("The file is currently in Read Only mode. The mac ro will not
be executed until you have full access to the file.")
        Exit Sub
    Else

'If it is not, it checks if the source file exists. If not, the user
receives a notification and the macro stops
        If CreateObject("Scripting.FileSystemObject").FileExists _
            (Environ("HOMEDRIVE") & Environ("HOMEPATH") &
"DesktopBO-OL.XLS") = False Then
            MsgBox ("The source file wasn't found. The macro will not be
executed")
            Exit Sub
        Else

'If both requirements are satisfied, then the macro begins
'We begin by opening the source file
            Workbooks.Open Filename:= _
                Environ("HOMEDRIVE") & Environ("HOMEPATH" ) &
"DesktopBO-OL.XLS"

'The variable for the loop depends on the number of line there is in the
source file
            For i = 3 To Workbooks("BO-OL.XLS").Sheets("Rep ort
1").Range("A1").Value + 2
'If the line of our Order List correspond to the one in the source file, it
goes to the next line
            If Workbooks("BO-OL.XLS").Sheets("Report 1").Cell s(i, 1) =
ThisWorkbook.Sheets("2009").Cells(i, 1) Then
            Next i
            Else
'Otherwise we copy the entire line of the source file and paste it at the
same place in our Order List
                Workbooks("BO-OL.XLS").Sheets("Report 1") .Rows(i).Copy
                ThisWorkbook.Sheets("2009").Rows(i).Paste
            Next i
                Workbooks("BO-OL.XLS").Sheets("Report 1") .Columns(2).Copy
                ThisWorkbook.Sheets("2009").Columns(2).Pa ste
                Workbooks("BO-OL.XLS").Close
            End If
        End If
    End If

Application.ScreenUpdating = True

End Sub


Avatar
LSteph
Bonjour,
Tu as deux next i pour un seul for sans rien d'autre entre then et
else
en plus ils sont imbriqués à cheval dans le if:

Then
Next i '1 ici
Else
'Otherwise ..Paste
Next i ' et 1 second là
Workbooks...Close
End If

..amha faudrait structurer uniquement le If dans le For
ou selon ce qui est voulu autrement
mais pas une partie enchevêtrée dans l'autre. ...a priori...

--
lSteph
...
On 20 juil, 14:09, YeTi wrote:
Bonjour à tous,

Je cherche à mettre à jour automatiquement un fichier Excel avec un r apport
Business Objects exporté sous Excel. Le problème c'est que lorsque je lance
ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai b ien
un "For" déclaré avant le Next...

Quelqu'un a-t-il une solution?

Le code se trouve ci-dessous

Merci d'avance

Private Sub Workbook_Open()

Application.ScreenUpdating = False

'Checks if the file is on Read Only mode. If it is, the user receives a
notification and the macro stops
    If ThisWorkbook.ReadOnly = True Then
        MsgBox ("The file is currently in Read Only mode. The mac ro will not
be executed until you have full access to the file.")
        Exit Sub
    Else

'If it is not, it checks if the source file exists. If not, the user
receives a notification and the macro stops
        If CreateObject("Scripting.FileSystemObject").FileExists _
            (Environ("HOMEDRIVE") & Environ("HOMEPATH") &
"DesktopBO-OL.XLS") = False Then
            MsgBox ("The source file wasn't found. The macro will not be
executed")
            Exit Sub
        Else

'If both requirements are satisfied, then the macro begins
'We begin by opening the source file
            Workbooks.Open Filename:= _
                Environ("HOMEDRIVE") & Environ("HOMEPATH" ) &
"DesktopBO-OL.XLS"

'The variable for the loop depends on the number of line there is in the
source file
            For i = 3 To Workbooks("BO-OL.XLS").Sheets("Rep ort
1").Range("A1").Value + 2
'If the line of our Order List correspond to the one in the source file, it
goes to the next line
            If Workbooks("BO-OL.XLS").Sheets("Report 1").Cell s(i, 1) =
ThisWorkbook.Sheets("2009").Cells(i, 1) Then
            Next i
            Else
'Otherwise we copy the entire line of the source file and paste it at the
same place in our Order List
                Workbooks("BO-OL.XLS").Sheets("Report 1") .Rows(i).Copy
                ThisWorkbook.Sheets("2009").Rows(i).Paste
            Next i
                Workbooks("BO-OL.XLS").Sheets("Report 1") .Columns(2).Copy
                ThisWorkbook.Sheets("2009").Columns(2).Pa ste
                Workbooks("BO-OL.XLS").Close
            End If
        End If
    End If

Application.ScreenUpdating = True

End Sub