Compile error: Next without For

joshward10 · #1 (**permalink**) 03-01-11, 12:32

I received the error "next without for" when trying to add the msgbox bit of code. I thought by saying next projectNum, it would return to the For function at the top. What am I missing?

Thanks in advance.

Code:

Sub RunLoop()
application.ScreenUpdating = False
currentPeriod = "3"

    For ProjectNum = 2 To projects.Range("a6000").End(xlUp).Row
        ThisWorkbook.Activate
        projectname = projects.Range("A" & ProjectNum)
        Filename = projects.Range("B" & ProjectNum)
        Workbooks.Add "d:\personal\Forecast Files\Tools\Report Puller Template"
        Workbooks("Report Puller Template1").Activate
        
        'Write ProjectName into template
        Range("A4") = projectname
        X = EssVRetrieve(Empty, Range("A1:n100"), 1)
        If X = 0 Then
        Else
        MsgBox ("Retrieve failed.")
        End
        End If
        
        'If the project name doesn't match essbase project name- error box.
        If "A84" = 1 Then
            MsgBox projectname & "has errored during the process, it will need to be done manually. Make sure the project name is correct in the template.  The process will now skip to the next project."
            ActiveWindow.Close SaveChanges:=False
            Next ProjectNum
            Else
        End If
        
        
        'Format Template before saving
        Range("B5:N5").ClearContents
        Range("O85") = projects.Range("C" & ProjectNum)
        Columns("A:A").EntireColumn.AutoFit
    
        'Save and Close
        ActiveWorkbook.SaveAs Filename:="d:\personal\Forecast Files\" & Filename & "_P" & currentPeriod & ".xls"
        ActiveWindow.Close
        ThisWorkbook.Activate
    Next ProjectNum
    
application.ScreenUpdating = True
End Sub

Colin Legg · #2 (**permalink**) 03-01-11, 13:22

Hi,

It's because you've put the Next within an If...End If block. It's then duplicated further down in the code.

Code:

        'If the project name doesn't match essbase project name- error box.
        If "A84" = 1 Then
            MsgBox projectname & "has errored during the process, it will need to be done manually. Make sure the project name is correct in the template.  The process will now skip to the next project."
            ActiveWindow.Close SaveChanges:=False
            Next ProjectNum
        Else
 
        End If

Presumably it should be structured more like this?

Code:

        'If the project name doesn't match essbase project name- error box.
        If "A84" = 1 Then
            MsgBox projectname & "has errored during the process, it will need to be done manually. Make sure the project name is correct in the template.  The process will now skip to the next project."
            ActiveWindow.Close SaveChanges:=False
        Else
            'Format Template before saving
            Range("B5:N5").ClearContents
            Range("O85") = projects.Range("C" & ProjectNum)
            Columns("A:A").EntireColumn.AutoFit
        
            'Save and Close
            ActiveWorkbook.SaveAs Filename:="d:\personal\Forecast Files\" & Filename & "_P" & currentPeriod & ".xls"
            ActiveWindow.Close
            ThisWorkbook.Activate
        End If
    Next ProjectNum

joshward10 · #3 (**permalink**) 03-01-11, 15:15

Thanks for the reply. I found a similar solution using the GoTo function. Only problem now is that the if statement flat out doesnt work. Even if there is a value in A84, the macro will continue running without throwing the message box and closing the active window like I told it to. Ugh...

Code:

'If the project name doesn't match essbase project name- error box.
        If Worksheets("expense").Cells(84, 1) = 1 Then
            MsgBox projectname & "has errored during the process, it will need to be done manually. Make sure the project name is correct in the template.  The process will now skip to the next project."
            ActiveWindow.Close SaveChanges:=False
            'Next ProjectNum
            GoTo projend:
        End If
        
        'Format Template before saving
        Range("B5:N5").ClearContents
        Range("O85") = projects.Range("C" & ProjectNum)
        Columns("A:A").EntireColumn.AutoFit
    
        'Save and Close
        ActiveWorkbook.SaveAs Filename:="d:\personal\09069595\Research and Development\Forecast Files\" & Filename & "_P" & currentPeriod & ".xls"
        ActiveWindow.Close
        ThisWorkbook.Activate

projend:
    Next ProjectNum

Colin Legg · #4 (**permalink**) 03-01-11, 16:09

Hi Josh,

The VBA GoTo statement should only ever be used as a directive to branch to error handling code. This is because using it results in "spaghetti" code which is hard to follow (jumping all over the place). What's wrong with the layout I suggested - I'm happy to work on it with you?

joshward10 · #5 (**permalink**) 03-02-11, 10:02

Ok, I changed my code to what you suggested and it works fine... still not sure why the GoTo is such a bad idea, but I guess yours is a little easier to read. I'm still having the problem of the message box not popping up though.

The problem I'm having is that sometimes people change the name of a project in essbase, so when I have excel read down the list of project names I typed in, they won't match up and excel will just load a ton of numbers that make no sense. It will also shift all the cells down one row for some reason. So A84 normally will be empty but if someone changes the project name and essbase pulls the wrong data, everything will shift down one row and A84 will then have a value. It will actually say 'Expense. That's why I tried using this code to alert the user of the error.

Code:

If Worksheets("expense").Cells(84, 1) = 1 Then
            MsgBox projectname & "has errored during the process, it will need to be done manually. Make sure the project name is correct in the template.  The process will now skip to the next project."
            ActiveWindow.Close SaveChanges:=False

It won't throw the error though and just continues on like nothing is wrong. I've tried If "A84" = 1 Then... and also If "A84" = "'Expense" but nothing will make the msgbox trip. Any ideas?

Internet Services

Web Development

Digital Marketing

Consumer Tech