Loop Without Do

[Guest]

Please could someone take a look athis code and tell me why I keep getting
the compile error "Loop Without Do", on the second loop. The first loop
works, but the second loop returns a compile error. Any assistance will be
appreciated.

Sub Create_TXT()

Application.DisplayAlerts = False
For Each sh In Worksheets
If sh.Name = "TXT" Then sh.Delete
Next
Sheets.Add
ActiveSheet.Name = "TXT"

'Create lines

k = 0 '(row for source tab)
For Each sh In Worksheets

If sh.Name = "BSPL+" Then
sh.Activate
nb_sbe = Rows("1").Find("Total").Column - 9 - 1
'possible issue is 1 sbe so no total column
i = 2 '(row for summary tab)
'For each row with data
Do While Range("B" & i) <> Empty
'only rows with non result subitem
If Range("E" & i) <> "Result" And Range("A" & i) <> "BSPL" Then
'check for each sbe
For j = 1 To nb_sbe + 1 '(column for summary tab)
'check if there is a value to be reported
If Cells(i, j + 9) <> 0 And Left(Cells(i, j + 9).FormulaR1C1,
10) <> "=SUM" Then
k = k + 1
Sheets("TXT").Range("A" & k) = k 'counter
Sheets("TXT").Range("M" & k) = Range("C" & i) 'item
Sheets("TXT").Range("Q" & k) = Cells(i, j + 9) 'value
Sheets("TXT").Range("S" & k) = Range("E" & i) 'subitem
If Range("E" & i) = "" Then Sheets("TXT").Range("S" &
k) = " "
'info either on SBE level or on ru level
If Len(Cells(1, j + 9)) = 4 Or j = nb_sbe + 1 Then
Sheets("TXT").Range("T" & k) = Cells(1, j + 9) 'sbe
Sheets("TXT").Range("U" & k) = " " 'ru
If j = nb_sbe + 1 Then Sheets("TXT").Range("T" & k) =
"COMP"
Else
Sheets("TXT").Range("T" & k) = " " 'sbe
Sheets("TXT").Range("U" & k) = Cells(1, j + 9) 'ru
If Cells(1, j + 9) = "NOTHIN" Then
Sheets("TXT").Range("U" & k) = " "
End If

Sheets("TXT").Range("V" & k) = Range("G" & i) 'tp
If Range("G" & i) * 1 = 0 Then Sheets("TXT").Range("V" &
k) = " "
'check partner sbe and ru
Sheets("TXT").Range("W" & k) = " "
'partner sbe
Sheets("TXT").Range("X" & k) = " "
'partner ru
Sheets("TXT").Range("Y" & k) = Range("H" & i) 'ctry
If Range("H" & i) = 0 Then Sheets("TXT").Range("Y" & k)
= " "
Sheets("TXT").Range("Z" & k) = Range("I" & i) 'prod
grp
If Range("Z" & i) = 0 Then Sheets("TXT").Range("Z" & k)
= " "
End If
Next
End If
i = i + 1
Loop
End If
Next

For Each sh In Worksheets
If sh.Name = "BSPL+" Then
sh.Activate
nb_sbe = Rows("1").Find("Total").Column - 9 - 1
'possible issue is 1 sbe so no total column
i = 2 '(row for summary tab)
'For each row with data
Do While Range("B" & i) <> Empty
'only rows with non result subitem
If Range("E" & i) <> "Result" And Range("A" & i) <> "BSPL" Then
'check for each sbe
For j = 1 To nb_sbe + 1 '(column for summary tab)
'If Cells(i, j + 9) <> 0 And Left(Cells(i, j + 9).FormulaR1C1,
10) <> "=SUM" Then
'insert OMI if needed
If Not Sheets("OMI Mapping").Columns("A").Find(Range("C" &
i)) Is Nothing Then
m = Sheets("OMI Mapping").Columns("A").Find(Range("C" &
i)).Offset(0, 4)
omi_si = Sheets("OMI Mapping").Columns("A").Find(Range("C"
& i)).Offset(0, 9)
If m <> "no" Then
k = k + 1
Sheets("TXT").Range("A" & k) = k
'counter
Sheets("TXT").Range("M" & k) = m 'OMI item
Sheets("TXT").Range("Q" & k) = Cells(i, j + 9)
'value
Sheets("TXT").Range("S" & k) = omi_si
'OMI subitem
If Len(Cells(1, j + 9)) = 4 Or j = nb_sbe + 1 Then
Sheets("TXT").Range("T" & k) = Cells(1, j + 9) 'sbe
Sheets("TXT").Range("U" & k) = " " 'ru
If j = nb_sbe + 1 Then Sheets("TXT").Range("T" & k) =
"COMP"
Else
Sheets("TXT").Range("T" & k) = " " 'sbe
Sheets("TXT").Range("U" & k) = Cells(1, j + 9) 'ru
If Cells(1, j + 9) = "NOTHIN" Then
Sheets("TXT").Range("U" & k) = " "
End If
Sheets("TXT").Range("V" & k) = Range("G" & i) 'tp
If Range("G" & i) * 1 = 0 Then Sheets("TXT").Range("V" &
k) = " "
'check partner sbe and ru
Sheets("TXT").Range("W" & k) = " "
'partner sbe
Sheets("TXT").Range("X" & k) = " "
'partner ru
Sheets("TXT").Range("Y" & k) = Range("H" & i) 'ctry
If Range("H" & i) = 0 Then Sheets("TXT").Range("Y" & k)
= " "
Sheets("TXT").Range("Z" & k) = Range("I" & i) 'prod
grp
If Range("Z" & i) = 0 Then Sheets("TXT").Range("Z" & k)
= " "

End If
i = i + 1
Loop
End If
Next

 

[Guest]

You are missing a "Next" for the second "For" and it looks like there are
some missing "End If"s unless the line wrap has changed your original line
settings.

 

[Guest]

Hi
Hazarding a quick guess

It looks like you are missing a few End If statement in that 2nd Do .... Loop
which would throw that exception

I count 10 If Statements, and only 3 End If Statements
2 For .... Statements, only 1 Next Statement

HTH

 

[Guest]

I suspect that it is your If ... Then statements that are causing the prob
lem. It looks like some are blocked and others are not. Hard to tell from
the news reader.
If you have a blocked If statement without an End If inside the Do loop, it
could be causing the compiler to think that the Do loop is open. I always
try to close my If statements as I go, where practicable, to avoid these type
errors.

 

[Don Guillett]

Maybe you don't have a nex j. Probably could be more efficient with select
case.