Compile Error : Loop without Do

[Kathy Belvel]

I have written a macro (see below) and when I step through it I keep getting a Compile error: Loop without Do. but there is a Do While statement. I have searched through the VBA and Macros book and the web and can find no resolution. In the coding below I have tried to be as transparent as possible with my comments. As for the code itself, I'm new to this so it may not be very elegant.


Thanks in advance,

Kathy


Sub mcrSelectTeam1()
‘
' mcrSelectTeam1 Macro
'
' Declare variables to be used during macro execution
'
Dim t As Integer ' Defines "t" as counter for current Team size
Dim i As Integer ' Defines first row of HSTP Team table
'
' Declare Temporary Arrays and supporting variables and counters
'
Dim QualifiedArray() As Variant ' Defines temp multi-dim array with number of rows = to the number of Qualifiers and 3 columns
Dim q As Integer ' Defines "q" as number of Qualifiers (cannot be greater than 6)
Dim qc As Integer ' Defines Qualifier counter
Dim qm As Integer ' Defines "qm" as counter for Qualified List number of rows
Dim ql As Integer ' Defines Qualifier members list counter

Dim AlternateArray() As Variant ' Defines Temp multi-dim array with number of rows = to the number ' of Alternates and 3 columns
Dim a As Integer ' Defines "a" as number of Alternate players
Dim ac As Integer ' Defines Alternate counter
Dim am As Integer ' Defines "am" as counter for Alternate List number of rows
Dim al As Integer ' Defines Alternate members list counter
‘
' Assign values to variables being used during macro execution
‘
‘ In the Team worksheet is a 3 column table (B4;D28) of players signed up to participate in a golf tournament. The 3 columns are labeled;
‘ Team – Q or A (Q = Qualified – limited to 6 players; A = Alternate)
‘ Name – Players Name
‘ GHINN – players GHINN number
‘ At the bottom of the table are 3 count functions which count;
‘ 1. The number of players in the table in Name column (B29)
‘ 2. The number of Qualified players “Q” in Team column (B31)
‘ 3. The number of Alternate players “A” in Team column (B32)
‘
i = 4 ' Set starting row # for traversing HSTP Team table
q = Sheets("Team").Range("B31").Value ' Set counter "q" to Qualified Count = number of Qualified players
qc = 1 ' Set "qc" to initial row in temp QualifiedArray
ql = 9 ' Set "ql" to row number of Qualified list in Roster worksheet
ac = 1 ' Set "ac" to initial row in temp AlternateArray
al = 18 ' Set "al" to row number of Alternate list in Roster worksheet
a = Sheets("Team").Range("B32").Value ' Set counter "a" to Alternate Count = number of Alternate players
t = Sheets("Team").Range("B29").Value ' Set counter "t" to Team Count

ReDim QualifiedArray(1 To q, 1 To 3) ' Set array to "q" rows and 3 columns
ReDim AlternateArray(1 To a, 1 To 3) ' Set array to "a" rows and 3 columns

'
'Select and Unprotect the Team worksheet
'
Sheets("Team").Select ' Make Team worksheet active worksheet
ActiveSheet.Unprotect ' Unprotect Team worksheet
‘
‘ The following Do While loop will traverse through the table to find all Qualified (Q) and Alternate (A) players and copy
‘ the 3 values of each row into the corresponding rows and cells of the temporary Qualified and Alternate arrays.
‘
Do While i < t + 4 ' Start Do While loop
If Cells(i, 2).Value = "Q" Then
QualifiedArray(qc, 1).Value = Sheets("Team").Cells(i, 2).Value
QualifiedArray(qc, 2).Value = Sheets("Team").Cells(i, 3).Value
QualifiedArray(qc, 3).Value = Sheets("Team").Cells(i, 4).Value
qc = qc + 1
i = i + 1
ElseIf Cells(i, 2).Value = "A" Then
AlternateArray(ac, 1).Value = Sheets("Team").Cells(i, 2).Value
AlternateArray(ac, 2).Value = Sheets("Team").Cells(i, 3).Value
AlternateArray(ac, 3).Value = Sheets("Team").Cells(i, 4).Value
ac = ac + 1
i = i + 1
Else
i = i + 1
Loop ' Continue Do While Loop
ActiveSheet.Protect ' Re-Protect Team worksheet

Sheets("Roster").Select ' Make Roster worksheet active worksheet
ActiveSheet.Unprotect ' Unprotect Roster worksheet
‘
‘ For Next loops will copy the Names and GHINN numbers from the 2 temp Arrays (Qualified and Alternate) into
‘ the following 2 tables in the Roster worksheet;
‘ 1. Qualified Players table (D9;E14)
‘ 2. Alternate Players table (D18;E36)
‘
For qm = 1 To q
Sheets("Roster").Cells(ql, 4).Value = QualifiedArray(qm, 2).Value
Sheets("Roster").Cells(ql, 5).Value = QualifiedArray(qm, 3).Value
ql = ql + 1
Next qm

For am = 1 To a
Sheets("Roster").Cells(al, 4).Value = AlternateArray(am, 2).Value
Sheets("Roster").Cells(al, 5).Value = QualifiedArray(am, 3).Value
al = al + 1
Next am

ActiveSheet.Protect ' Re-Protect Roster worksheet

End Sub

 

[Michael M]

your missing an End If statement before the end of the Loop