Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Compile error: Next without For

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    508
    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

  3. #3
    Join Date
    Mar 2011
    Posts
    3
    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

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    508
    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?

  5. #5
    Join Date
    Mar 2011
    Posts
    3
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •