next without For - help please?

[jayd4wg]

For Each cell In [Data!a5:a279999]
    If cell.EntireRow.Hidden = False Then
        pm_name = cell.Offset(0, 7)

'define variables values in subroutine def_Vars
    def_Vars
'catch end of document to exit loop
        If pm_name = "" Then
            Exit For
        End If
'write the placemark info to file #1
'if account is not parent then write folder closing statements to kml and set trigger to only write it once
'then write as child folder output
    If pm_par = 0 Then
    write_folderClose
    End If
    write_pmOutput
        
Next cell
'write footer to file
Print #1, "</folder>"
Print #1, "</Document>"
Print #1, "</kml>"

Close #1
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

the write_folderClose sub is to write a one time bit of code to close out a kml folder (for google earth)

Sub write_folderClose()
If parent_Trigger = True Then
Print #1, "</folder>"
Print #1, "<folder><name>Child Accounts</name><open>0</open>"
Else
parent_Trigger = False
End If

End Sub

the write_pmOutput sub has nothing more in it than a bunch of print statements to write the KML code for a bunch of variables collected from the spreadsheet.

it befuddles me as to how i'm losing the for/next relationship...any thoughts? This macro is failing to compile because of a next without for error. i'm STUMPED

 

[Jonmo1]

You're actually missing an End If

For Each cell In [Data!a5:a279999]
    If cell.EntireRow.Hidden = False Then
        pm_name = cell.Offset(0, 7)
    End If

 

[shg]

If you indent your code (Office Automation Ltd. - Smart Indenter), it looks like this:

    For Each cell In [Data!a5:a279999]
        If cell.EntireRow.Hidden = False Then
            pm_name = cell.Offset(0, 7)

            'define variables values in subroutine def_Vars
            def_Vars
            'catch end of document to exit loop
            If pm_name = "" Then
                Exit For
            End If
            'write the placemark info to file #1
            'if account is not parent then write folder closing statements to kml and set trigger to only write it once
            'then write as child folder output
            If pm_par = 0 Then
                write_folderClose
            End If
            write_pmOutput

        Next cell
        'write footer to file
        Print #1, ""
        Print #1, ""
        Print #1, ""

        Close #1
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic

See the problem?

 

[Rick Rothstein]

The problem actually is that you are missing an "End If" statement for the first "If..Then" statement, the one immediately under the "For" statement. The problem is the VB does not examine each structure individually, rather it treats them all like they were the same and simply counts the opening and closing statements for all blocks... when they do not balance, VB simply assumes the last block structure is at fault, hence the "For Missing Next" error.

 

[jayd4wg]

could it have anything to do with not declaring "cell" as a variable? should it be declared? this worked just fine until i chopped the macro up into a few subs.

the whole reason i went to the sub structure for all the code is that it was getting really messy to look at and debug, with alot of duplicated code. I went from a flat kml document with parent accounts and child accounts mapping all on the same folder and sr. leadership has asked that i put parents in a separate folder from child accts. so that the child accounts layer (folder) can be turned off in google earth so they don't display.

 

[jayd4wg]

bloody good catch! thank you!

 

[Jonmo1]

You're welcome.

 

[DocAElstein]

By looking at the code my guess as well would be the missing End If. As a beginner I was continually stumped by the strange “next without For” Compiling error in Code where clearly the For and Next were indeed there! It took a while before I noticed the peculiarity that VBA gave this error message if there was a missing End If within the Loop. I have been finding a lot of these small quirks that are typically not shown or discussed in most books. Hence the usefulness of this Forum where experienced users give the benefit of their knowledge.
Making a habit of always typing things like For and Next or If and End If at the same time with a staggering indent I found to mostly ensure that I do not make so many mistakes.

 

[Rick Rothstein]

Making a habit of always typing things like For and Next or If and End If at the same time with a staggering indent I found to mostly ensure that I do not make so many mistakes.

That's what I do... I type the opening block statement, hit the Enter Key twice, then type the closing block statement, then hit the Up Arrow Key (puts me on the blank line created by the double Enter Key) and press the Space Bar twice (I use a double space for my indents)... at this point, I am ready to begin typing the code that goes into the block structure. After many years of programming, this has become second nature... I haven't seen a "You Are Missing" error in too many years to count now.

 

[Jonmo1]

What makes it even harder to troubleshoot (aside from the indentation) is that you can't just count the IF's and assume you need the same number of End If's. Because you can have 1 line IF's that have the 'actioniftrue' part written on the same line as the IF condition. So no End If is required. Then, once you know you're missing an End If, it's not always as easy as it was in this case to figure out where to put a new End If. Putting it in the wrong place can completely change what the code is intended to do, even though it will compile without error.