NutoVBA


Please could you take a look at the code below and advise why the second loop returns a complie error - "Loop without Do" The first loop works, but for some reason, the second loop does not work. Any help 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+" Or sh.Name = "Other OMI" Then
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+" Or sh.Name = "Other OMI" Then
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




Re: Loop Without Do

Peter Mo.


Hi

This sort of error message usually means you have missed off an "End If". Actually, having a quick look at your code I think you might have missed off an "End If", "Next J", and another "End If" for towards the end of the second loop.

The usual way I tackle this is to make sure my code is laid out precisely with indents in loops and if statements, etc. It is then easier to see what is missing as the If/End If pairs etc. can be matched visually.

Regards

Peter