Compile Error: Next Without For
Le
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
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
Poser une question
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
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