How do I Execute a Conditional Loop

Posted on 2011-10-25
Medium Priority
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
  • 3
  • 2
LVL 75

Expert Comment

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

Author Comment

ID: 37024869
So that will restart the loop?

LVL 75

Accepted Solution

käµfm³d   👽 earned 2000 total points
ID: 37024903
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

ID: 37027259
This was exactly what I was looking for.

LVL 75

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

839 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