If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Compile error: Next without For

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
__________________
Colin

RAD Excel Blog
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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?
__________________
Colin

RAD Excel Blog
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On