How do I Execute a Conditional Loop

Posted on 2011-10-25
Last Modified: 2012-06-27
I'm using code to read a column of data records out of an Excel file and I want to restart the beginning of a loop when I hit the end of the column, which is indicated with the value 99.

When I put in this code:
If lngCurrentTeamID = 99 Then
End if

It throws an error. Obviously entering the Loop command in inside the If/Then loop confuses the code. The entire code segment is attached. When I hit the 99 in the column, I want to jump back to the top of the loop. How would I do that?

Do Until boolEOF = False
            With objExcel
                lngCurrentPublicationID = .Cells(intDataRow, intDataColumn)
                If lngCurrentPublicationID = 99 Then
                    boolEOF = False
                    Exit Do
                End If
                intDataRow = 1
                intDataColumn = 3
                lngCurrentTeamID = .Cells(intDataRow, intDataColumn)
            End With

        strSQL = "SELECT Sum(RevenueRecords.Revenue) AS SumOfRevenue, Issues.IssueClosed " & _
            " FROM ((Issues INNER JOIN Publications ON Issues.PublicationID = Publications.PublicationID) " & _
            " INNER JOIN RevenueRecords ON Issues.IssueID = RevenueRecords.IssueID) INNER JOIN " & _
            " (SalesReps INNER JOIN SalesTeams ON SalesReps.TeamID = SalesTeams.TeamID) ON " & _
            " RevenueRecords.SalesRepID = SalesReps.RepID GROUP BY Publications.PublicationID, SalesTeams.TeamID, Issues.IssueClosed " & _
            " HAVING Publications.PublicationID = " & lngCurrentPublicationID & " AND SalesTeams.TeamID = " & lngCurrentTeamID & _
            " AND Issues.IssueClosed = False"

Open in new window

Question by:Buck_Beasom
    LVL 74

    Expert Comment

    by:käµfm³d 👽
    Use Continue Do, not Loop. Use Loop for the end of the loop.

    Author Comment

    So that will restart the loop?

    LVL 74

    Accepted Solution

    It will go back to the beginning of the loop, yes. This is, of course, if you condition has not changed. If the boolean that controls the loop changes state, then your loop will end. This should be expected, though.

    Let me clarify that this will not cause your code to go back to a state similar to if the loop had never run. It will just cause your code to jump back to the beginning of the loop logic and skip anything that comes after it. If you are familiar with assembly (don't sweat it if you're not), this is basically a jump back to the start of the loop. No application state is changed other than what the next instruction to execute is.

    Author Closing Comment

    This was exactly what I was looking for.

    LVL 74

    Expert Comment

    by:käµfm³d 👽
    NP. Glad to help  = )

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now