Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Without Do
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Without Do
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. -- Best wishes, Jim "NutoVBA" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Without Do
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 "NutoVBA" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Without Do
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. "NutoVBA" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |